Evil Science A whole load of stuff

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
Filed under: Main Leave a comment
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment


eight − = 2

No trackbacks yet.