Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Unanswered: Cycle cells in Given Range

    I have an excel sheet and I am trying to write a macro that compares values in 2 rows and highlights the cell depending on an if statement. I would like the macro to compare only corresponding cells not any values in each row. For example i want to code to compare b6 to b8, c6 to c8... so on and so on. If the value in row 8 is higher than in row 6 I would like the macro to highlight the cell in row 8 with green, and if it is lower than row 6 I would like the macro to highlight the value in row 8 with red. I've tried a couple things but have no idea what kind of code I need for this and what will be the most efficient method. If anyone could help me with this I would greatly appreciate it. Also the code would be attached to a button that I have in the excel document. I have also attached a screen shot of the excel sheet for reference. Thank you in advance.
    Attached Thumbnails Attached Thumbnails Excel Print Screen.jpg  

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by MJnov85
    I have an excel sheet and I am trying to write a macro that compares values in 2 rows and highlights the cell depending on an if statement. I would like the macro to compare only corresponding cells not any values in each row. For example i want to code to compare b6 to b8, c6 to c8... so on and so on. If the value in row 8 is higher than in row 6 I would like the macro to highlight the cell in row 8 with green, and if it is lower than row 6 I would like the macro to highlight the value in row 8 with red. I've tried a couple things but have no idea what kind of code I need for this and what will be the most efficient method. If anyone could help me with this I would greatly appreciate it. Also the code would be attached to a button that I have in the excel document. I have also attached a screen shot of the excel sheet for reference. Thank you in advance.
    Hi

    I think this should give you a starting point
    Code:
    Sub TEST()
        Dim iCol As Long
        iCol = 2
        Do Until Cells(6, iCol) = ""
            Select Case True
                Case Cells(8, iCol) > Cells(6, iCol)
                    Cells(8, iCol).Interior.ColorIndex = 35
                Case Cells(8, iCol) < Cells(6, iCol)
                    Cells(8, iCol).Interior.ColorIndex = 3
                Case Else
                    Cells(8, iCol).Interior.ColorIndex = xlNone
            End Select
            iCol = iCol + 1
        Loop
    End Sub
    ??

    MTB

    p.s. you could also use conditional formatting ??
    Last edited by MikeTheBike; 06-29-09 at 11:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •