Evil Science A whole load of stuff


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

End Sub
Filed under: Main Leave a comment
Comments (0) Trackbacks (0)

No comments yet.

Leave a comment

4 × = thirty two

No trackbacks yet.