Excel Highlight a row according to a change in a specific cell

am using Excel 2016 and i want an advice on how to do this: i have an excel table and i want to highlight each row basing on a change in a cell in row and change a color for example if c2,c3,c7 cont...

excel

Claude080

Copper Contributor

Feb 06, 2022

I have a table in an Excel worksheet where the rows are generally grouped (perhaps sorted) by the value in one column. In the below example, it is Column A and it is sorted by Year. But it's not necessarily numeric and it's not necessarily sorted; it could be "Apple", "Apple", "Apple", "Pear", "Banana", "Banana".
So the fill for the rows with 1999 in the Year column would be one color, say orange, then when the value changes, the fill color changes. It would be fine if the color just alternated, say orange then green then orange, etc. I'm interested in a general way of doing it, not something that relies on the column being years or a number, it could be a car make, or a fruit, etc. Also, if there's another year 1999 many rows down, it need not be fill color 1, it just has to be different from the non-1999 rows adjacent to it.

I've used conditional formatting for several things but I can't get it to do this. The purpose is to be able to better see when the year changes. This is different from just alternating the fill.

HansVogelaar

MVP

Feb 06, 2022

Claude080

Here is a macro:

Sub ColorRows()
    Dim d As Object
    Dim r As Long
    Dim m As Long
    Dim v As Variant
    Dim c As Long
    Application.ScreenUpdating = False
    Set d = CreateObject("Scripting.Dictionary")
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To m
        v = Range("A" & r).Value
        If Not d.exists(v) Then
            d(v) = RGB(224 * Rnd + 32, 224 * Rnd + 32, 224 * Rnd + 32)
        End If
        Range("A" & r).EntireRow.Interior.Color = d(v)
    Next r
    Application.ScreenUpdating = True
End Sub

Forum Discussion

Excel Highlight a row according to a change in a specific cell

Resources