Home » Questions » Computers [ Ask a new question ]

Excel: highlight groupings of data differently based on a cell's value

Excel: highlight groupings of data differently based on a cell's value

Consider a sheet with a bunch of values grouped together. The goal is to have Excel shade the entire row's background color differently for each differing group of Requisition.

Asked by: Guest | Views: 27
Total answers/comments: 1
Guest [Entry]

"If you want to keep the color thing, you could automate by using two different colors, one for one group, other for the next group.

You can use the a little macro to achieve this.

Sub SetCustomColors()

Dim color As OLE_COLOR
Dim color1 As OLE_COLOR
Dim color2 As OLE_COLOR

Dim currentRequisition As Integer

color1 = &HF0F0F0 ' RR GG BB
color2 = &HF0F000 ' RR GG BB

currentRequisition = -1
For r = 3 To 16
If currentRequisition <> ActiveSheet.Cells(r, 1).Value Then
currentRequisition = ActiveSheet.Cells(r, 1).Value
color = IIf(color = color1, color2, color1)
End If
ActiveSheet.Range(ActiveSheet.Cells(r, 1), ActiveSheet.Cells(r, 4)).Interior.color = color
Next

End Sub"