24May/110
Excel Macro: colour alternate ranges of the same value in the specified column
' ' Process a column of values, colouring alternate ranges of the same value. ' ' Work down a column of cells, stopping when a blank is hit. ' ' If a cell is a different value to the one preceding it, invert the ' value of the toggle variable. ' ' If the toggle variable is true colour the cell. ' Public Sub main() Dim w As Worksheet Set w = Worksheets("Sheet1") Dim rowCtr As Integer rowCtr = 2 Dim toggle As Boolean toggle = False While w.Cells(rowCtr, 1).Value <> "" If rowCtr > 2 Then If w.Cells(rowCtr, 2).Value <> w.Cells(rowCtr - 1, 2).Value Then toggle = Not toggle End If End If If toggle Then With w.Cells(rowCtr, 2).Interior .ColorIndex = 6 .Pattern = xlSolid End With End If rowCtr = rowCtr + 1 Wend End Sub
Leave a comment