Format Excel CFD

format_cfd.vba

Sub ColorMatchingCells(toMatch, color, topLeft, bottomRight)
'
' Colour in the different states in the Cumulative Flow Diagram
'
 
    Range(topLeft, bottomRight).Select
    'Cells.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlTextString, _
                                                        String:=toMatch, _
                                                        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColor = color
        .ColorIndex = color
    End With
    Selection.FormatConditions(1).Font.ColorIndex = 27
    Selection.FormatConditions(1).StopIfTrue = False
End Sub
 
Sub LoopOverRange(colorLookupRange, topLeft, bottomRight)
 
    NumRows = Range(colorLookupRange, Range(colorLookupRange).End(xlDown)).Rows.Count
    For x = 0 To NumRows - 1
        Debug.Print (toMatch)
        toMatch = Range(colorLookupRange).Offset(x).Value
        color = Range(colorLookupRange).Offset(x, 1).Value
        Call ColorMatchingCells(toMatch, color, topLeft, bottomRight)
    Next
 
End Sub

Sub formatCFD()
    Call LoopOverRange("A51", "B2", "CJ49")
End Sub