Logic of the following VB macro can be commonly used for an EXCEL worksheet by doing some modification of the logic sequence. This macro will change the background color of cells when found the first occurrence of a value which is different to the previous value set.
Following hints will help you to identify how your logic sequence should be changed according to your requirement.
c = 5 To 100 : used to indicate range of the columns of the data set. According to this code snippets program will consider values from column 5 to column 100.
r = 2 To 100 : used to indicate range of the rows the data set. According to this code snippets program will consider values from row 5 to row 100.
If (comparisonValue <> Cells(c, r + 1).Value) And (Cells(c, r +
1).Value <> "" And comparisonValue <> "") : According to this IF condition (c, r+1) cell background color will be changed if its value is different to (c, r) value and (c, r) is not empty.
Sub FormatCells()
Dim comparisonValue As String
comparisonValue = ""
For c = 5 To 100
comparisonValue = ""
For r = 2 To 100
If Cells(c, r).Value <> "" Then
comparisonValue = Cells(c,
r).Value
End If
If (comparisonValue <> Cells(c, r + 1).Value) And (Cells(c, r +
1).Value <> "" And comparisonValue <> "") Then
Cells(c, r +
1).Interior.ColorIndex = 8
End If
Next r
Next c
End Sub
