VBA code to change row color when value in column A changes
Solution 1
Sub colorize()
Dim r As Long, val As Long, c As Long
r = 1
val = ActiveSheet.Cells(r, 1).Value
c = 4 '4 is green, 3 is red '
For r = 1 To ActiveSheet.Rows.Count
If IsEmpty(ActiveSheet.Cells(r, 1).Value) Then
Exit For
End If
If ActiveSheet.Cells(r, 1).Value <> val Then
If c = 3 Then
c = 4
Else
c = 3
End If
End If
ActiveSheet.Rows(r).Select
With Selection.Interior
.ColorIndex = c
.Pattern = xlSolid
End With
val = ActiveSheet.Cells(r, 1).Value
Next
End Sub
Add the following to the relevant Worksheet's module if you want the macro to fire every time a cell in column A changes value:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then
Exit Sub
End If
colorize
End Sub
Solution 2
This can be done using only Excel formulas and conditional formatting if you can add a column. You might not be able to alternate red/green, but only a color and no color.
Alternate Excel Row Color Based on Content
Jack
Updated on July 09, 2022Comments
-
Jack almost 2 years
My spreadsheet is set up like this:
I have a list of job numbers in column A and a corresponding list of Job titles in Column B. I need for all of the rows of a certain job number to be colored. e.g., rows 2-4 have "3705" in column A, so those rows would be colored red. Rows 5 & 6 have 4169 in column A and would be colored green. Rows 7-10 have 5518 in column A and would be colored red (alternate between red and green).
Can someone give me a VBA code that will do what I described above automatically/using a macro?
Thanks!!