Excel VBA Multiple Conditional Formatting

Question

I'm new to VBA. I know this isn't the best way to do what I'm doing, so any help on ideas for improvement and general methodology explanation would be greatly appreciated!

So I have three different formats I want. The lesser than, the greater than, and a blank cell format.

Pretty much my code runs across a row checking each value against a value at the very end of that row (goal). If it's greater or equal to the goal, it turns green. If it's lesser, red. If the goal cell is empty, all cells are blank (formatting is white background, black text). If a cell is empty, the cell is blank (white background).

I ran all 5 macros in the order, Lesser, Greater, Equal, Empty Goal, Empty Cell. This created a nice looking Excel with correct format.

When you change the goal number, the cells in that row change respectively. Great! However, when you add a new number to a previously empty goal, or delete a goal number, you have to run the macros all over again to get the formatting.

Side note: For my code, I recorded myself doing one row of what I wanted (except for Empty Cell), then just turned it into a loop down the additional rows.

Any idea on how to eliminate re-running macros? Ideally this Excel document will get sent to someone who doesn't know anything about VBA.

Sub Greater()
Dim i As Integer
Dim myRange As String
Dim myComparison As String
For i = 4 To 97
    myRange = "C" & i & ":BC" & i
    myComparison = "=$BD$" & i
    Range(myRange).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:=myComparison
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Next i
End Sub

x

Sub Lesser()
Dim i As Integer
Dim myRange As String
Dim myComparison As String
For i = 4 To 97
    myRange = "C" & i & ":BC" & i
    myComparison = "=$BD$" & i
    Range(myRange).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:=myComparison
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Next i
End Sub

x

Sub Equal()
Dim i As Integer
Dim myRange As String
Dim myComparison As String
For i = 4 To 97
    myRange = "C" & i & ":BC" & i
    myComparison = "=$BD$" & i
    Range(myRange).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:=myComparison
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Next i
End Sub

x

Sub EmptyGoal()
Dim i As Integer
Dim myRange As String
Dim myComparison As String
For i = 4 To 97
    myRange = "C" & i & ":BC" & i
    Range(myRange).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:=IsEmpty(Cells(i, 56))
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Next i
End Sub

x

Sub EmptyCell()
    Range("C4:BC97").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(C4))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Answer 1

1. Why not to add a macro that calls them all? Then set a button somewhere in the sheet to run it. (PS: You could easily do everything in the same Sub and for all the cells you don't need to loop through each one of them (you do now because you are setting absolute references with the ($$) this works as formulas in Excel, just lock the Column and you'll be fine)
2. On a side note, if you delete ranges among the original range set for the conditional formatting then add/delete something between, the format conditional will likely split for those ranges (range if specified is not locked, it changes and it's an expected behavior by Excel). However, if you do it for the whole column nothing happens if the user adds rows -this means, conditional formatting will remain for added rows- (same principle for rows when you add/delete columns).
   

For example, you may use these subs instead for GreaterLesserAndEqual.

Sub AddConditionalFormating()
Dim myRange As Range: Set myRange = Range("C4:BC97")
myRange.FormatConditions.Delete
Call AddGreaterLesserAndEqual(xlGreater, 198, 239, 206, 0, 97, 0)
Call AddGreaterLesserAndEqual(xlLess, 156, 0, 6, 255, 199, 206)
Call AddGreaterLesserAndEqual(xlEqual, 0, 97, 0, 198, 239, 206)
End Sub
Sub AddGreaterLesserAndEqual(OperatorToEvalaute, RColorFonT As Long, GColorFont As Long, BColorFont As Long, RInteriorColor As Long, GInteriorColor As Long, BInteriorColor As Long)
Const myComparison = "=$BD1"
Dim myRange As Range: Set myRange = Range("C4:BC97")
myRange.FormatConditions.Add Type:=xlCellValue, Operator:=OperatorToEvalaute, _
Formula1:=myComparison
myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority
myRange.FormatConditions(1).Font.Color = RGB(RColorFonT, GColorFont, BColorFont)
myRange.FormatConditions(1).Interior.Color = RGB(RInteriorColor, GInteriorColor, BInteriorColor)
myRange.FormatConditions(1).StopIfTrue = False
End Sub

Answer 2

Not sure if I'm late to the show on this one, but I thought I'd add my two cents. I believe part of your problem you are getting with your cells not updating has to do with with this code Selection.FormatConditions.Add Type:=xlExpression, Formula1:=IsEmpty(Cells(i, 56)). What is happening here is VBA is calculating this expression IsEmpty(Cells(i, 56)) to be TRUE or FALSE then that boolean value is being put into your conditional format. Those, it never updates since TRUE is always TRUE and FALSE is always FALSE. Using something similar to your other expression would work here =LEN(TRIM(C4))=0

Also, fun fact that might help you in your future marco-ing. You are able to put Cells(i, 56) or Cells(i, "BD") and excel will handle it correctly. Sometimes I have a hard time remembering which column that was when I come back to the code later.

Another thing that is cool about Conditional Formatting is that you can put a formula in the condition and excel will extrapolate it just like it does with formulas in cells. Meaning, if Cell "A1" contains =SUM(B1:C1) and you copy that cell and paste it in Cell "A2", "A2" will now contain =SUM(B2:C2) And the dollar sign will freeze that portion of the address to that location. So $A1 will make the column stay "A" but allow the row to change. But A$1 will allow the column to change but the row to stay the same. And finally $A$! will make that an "Absolute" location, so the column or row won't change.

This also works with the condition in the conditional format. If you make the Formula =C4=$BD4 then C4 will see it as =C4=$BD4, but C5 will see it as =C5=$BD5, and finally D6 would see it as =D6=$BD6. So using this knowledge, you don't actually need to do a for loop to loop through all your rows, just need to use the $ correctly.

Taking all of this into account, I made the following code which should accomplish the same as what you had before, and hopefully be a little more readable too.

Things to note, you currently have Cell=Goal and Cell>Goal formatted the same way, this could just be one conditional format that was Cell>=Goal, but I left them separate so you could potentially have separate formatting for each condition in the future. Also xlThemeColorLight1 and Dart1 were just showing as White on my excel, so I wasn't sure what color you'd actually want them to be. But you can use .Color = RGB(XXX, XXX, XXX) or .Color = XXXXXXX if you know the index number of the color.

    Sub CombinedMacro()

    Dim ws As Worksheet
    Dim rng1 As Range, rng2 As Range
    Dim rowStart As Integer, rowEnd As Integer

    Set ws = ActiveSheet
    rowStart = 14
    rowEnd = 17

    Set rng1 = ws.Range("C" & rowStart & ":BC" & rowEnd)
    Set rng2 = ws.Range("BD" & rowStart & ":BD" & rowEnd)

    rng1.FormatConditions.Delete
    rng2.FormatConditions.Delete

    With rng1
        'Condition if cell is empty
        .FormatConditions.Add xlExpression, , "=LEN(TRIM(C" & rowStart & "))=0"
        With .FormatConditions(1)
            .Interior.Color = vbYellow
            .StopIfTrue = False
        End With

        'Condition if cell is equal to goal
        .FormatConditions.Add xlExpression, , "=C" & rowStart & "=$BD" & rowStart
        With .FormatConditions(2)
            .Font.Color = 24832 'positive version of -16752384
            .Interior.Color = 13561798
            .StopIfTrue = False
        End With

        'Condition if cell is less than goal
        .FormatConditions.Add xlExpression, , "=C" & rowStart & "<$BD" & rowStart
        With .FormatConditions(3)
            .Font.Color = 393372 'positive version of -16383844
            .Interior.Color = 13551615
            .StopIfTrue = False
        End With

        'Condition if cell is greater than goal
        .FormatConditions.Add xlExpression, , "=C" & rowStart & ">$BD" & rowStart
        With .FormatConditions(4)
            .Font.Color = 24832 'positive version of -16752384
            .Interior.Color = 13561798
            .StopIfTrue = False
        End With
    End With

    With rng2
        'Condition if goal is empty
        .FormatConditions.Add xlExpression, , "=LEN(TRIM(BD" & rowStart & "))=0"
        With .FormatConditions(1)
            .Interior.Color = vbBlue
            .StopIfTrue = False
        End With
    End With

End Sub

Oh, also, The FormatCondition(#) gets increased by 1 for each condition you add to that range. So they will happen in the order you create them in, I saw you were using Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority but that just changes the order. So changing their order in the code should have the same affect.

I hope this helps you. Happy Marco-ing!