VBA to alternate row color in a table when cell value changes-CodePudding

I have a spreadsheet that is currently bringing in a table of data via Powery Query. This means that every time there is a refresh, the table length may change. I need columns C and D to alternate highlight colors when the value is not the same as the previous row. Can someone help me with some code for this? I provided an example of how the table should look each time it is refreshed through Power Query and the the VBA code runs.
Here is the screenshot from my worksheet:

CodePudding user response：

(1) Attempt with conditional formatting:
(Note: This will work correctly only if a value cannot appear later down that list).

Create a rule (or two rules, to be precise) based on a formula. According to your screenshot, I assume that your data starts at row 3 and you want to look at column C.
There is a rather easy formula that you can use to count the number of unique values of a list. The base formula was "borrowed" from

(2) Formatting with VBA is simple. The following code formats one column of an table (a table in VBA is the type ListObject. Pass the listobject and the column number as parameter:

Sub ColorRows(table As ListObject, columnNumber As Long)
    Dim cell As Range, isOdd As Boolean
    For Each cell In table.DataBodyRange.Columns(columnNumber).Cells
        With cell
            If .Offset(-1, 0) <> .Value Then isOdd = Not isOdd
            ' On a standard Office color scheme, 10 is kind of green and 8 is a dirty yellow
            .Interior.ThemeColor = IIf(isOdd, 10, 8)
            .Interior.TintAndShade = 0.8
        End With
    Next
End Sub

This is how the call could look like (adapt the sheet and the listObject to your needs):

Sub test()
    ColorRows ThisWorkbook.Sheets(1).ListObjects(1), 3
End Sub

Now calling this code automatically is a different story and unfortunately rather complicated - if you want/need, try https://stackoverflow.com/search?q=vba QueryTable After Refresh for some insights. An easy alternative is to trigger the formatting manually, eg by placing a button (or a shape) on your sheet that calls the code.

CodePudding user response：

The VBA to apply the format condition for the alternating coloring would be:

Public Sub alternatingColorSizeAndKind(rg As Range)

Dim fc As FormatCondition
With rg.FormatConditions
    .Delete
    Set fc = .Add(xlExpression, , "=($C1=$C2) ($C2=$C3)")
    fc.Interior.Color = 14348258
    
    Set fc = .Add(xlExpression, , "=($C1<>$C2)")
    fc.Interior.Color = 13431551
End With
    
End Sub

You have to pass the range of your table to this sub.

If you have a listobject/table then you call it like this:

Public Sub update()


Dim lo As ListObject
'>>> adjust the names to your needs
Set lo = ThisWorkbook.Worksheets("Sheet1").ListObjects("Pull_Data")

alternatingColorSizeAndKind lo.DataBodyRange

End Sub