Conditional formatting based on cell value in row above

Try something like this:

Sub mycolour()

Dim RowLoop As Range

Worksheets("Names").Activate
Set myrange = Range("A2:M19")
    For Each RowLoop In myrange.Rows                                                     ' check next row for colour
        If RowLoop.Cells(1) = RowLoop.Cells(1).Offset(-1) Then
            RowLoop.EntireRow.Interior.Color = RGB(255, 255, 135)                ' colour 1
        Else
            RowLoop.EntireRow.Interior.Color = RGB(255, 204, 153)                ' colour 2
        End If
    Next
End Sub

Thanks for the help, Andrew-R. However, the rows don't seem to be getting all the correct colours and I can't work out why. The first row in the range - row 2, should always be the same colour - colour 1 because the row above is a headder with the row name. However, depending on how the columns are sorted, row two is sometimes colour 2.

Mystifying!!

OK, not quite sure why the value in row 2 should ever be the same as the header, but let's fudge it. Change:

If RowLoop.Cells(1) = RowLoop.Cells(1).Offset(-1) Then

To:

If RowLoop.Cells(1) = RowLoop.Cells(1).Offset(-1) Or RowLoop.Row = myRange.Rows(1).Row Then

Originally Posted by Andrew-R

OK, not quite sure why the value in row 2 should ever be the same as the header, but let's fudge it. Change:

If RowLoop.Cells(1) = RowLoop.Cells(1).Offset(-1) Then

To:

If RowLoop.Cells(1) = RowLoop.Cells(1).Offset(-1) Or RowLoop.Row = myRange.Rows(1).Row Then

Still the same problem and not working correctly - all consecutive rows with the same col 1 value should be the same colour.

I am attaching the file. Also, this needs to be initiated with a toggle button as when the sort criteria changes then the Ref column (column 1) changes but I have that part working (in another test file)

OK, with you now. How's this:

Sub mycolour()

Dim RowLoop As Range

Worksheets("Names").Activate
Set myrange = Range("A4:M19")
    For Each RowLoop In myrange.Rows                                            
        If RowLoop.Cells(1) = RowLoop.Cells(1).Offset(-1) And RowLoop.Row <> myrange.Rows(1).Row Then
            RowLoop.EntireRow.Interior.Color = RowLoop.Cells(1).Offset(-1).Interior.Color
        Else
          If RowLoop.Cells(1).Offset(-1).Interior.Color = RGB(255, 255, 135) Then
            RowLoop.EntireRow.Interior.Color = RGB(255, 204, 153)
          Else
            RowLoop.EntireRow.Interior.Color = RGB(255, 255, 135) 
          End If
        End If
    Next
End Sub

I don't see how it can have deleted row 1, as there's nothing in there that references row 1 and certainly nothing to delete it.

It worked fine on the example workbook you - attached is my screenshot after running it.

Well that's amazing - I am not losing the header any more - but all rows in range are same colour.

From your attached image, that is exactly what I want.

Could you re-attach the file and I'll see if I can find any difference between yours and mine.

many thanks for all your help and so prompt a reply as well.

Not a problem - attached.

All rows in range change to yellow no rows in salmon as in your image.

Are you using excel 2003 as I am?

No, I'm using Excel 2010 - maybe it is just an issue with which colours can be displayed, but even when I save that book as an Excel 2003 file it saves a close approximation of the colours used.

The colours work OK - they're in the attachment that I posted, so it's not that. I'll have to study your code and see if I can understand it, I've never seen, let alone used RowLoop or offset(), the later I am assuming means the row above if offset(-1).

I'll have to give it a rest for the moment and get out for my daily walk before it gets dark.

Very many thanks for all your help, time and patience. Very much appreciated.

RowLoop is just a variable name, it's a loop going through all of the rows in the range. Offset is, as you've guessed, just a way to refer to a range a set number of rows and columns away from another one.

I'll be online later on if you want to pick this up.

Problem solved - For some reason, whether it's Excel 2003, or my computer, the RGB code for colours was not accepted. I changed it to ColorIndex= and the results came out perfectly. And using a toggle button to activate and deactivate the colour scheme makes life much easier and also easier to understand the whole worksheet.

Many thanks again for all your time and trouble, Andrew-R.