VBA Conditional formating based on another cell

[blitz19]

Hello all,

I have run into a very common issue from what I can see, I need more than the allowed 3 conditional formats. I would like to format cells in range D17:D300 based on B17:300. The cell in column B could have a 1,2,3,4, or "E" in it and I would like to change the fill pattern of the coresponding cell in D based on B.
My code thus far will allow me to change a cell based on its own contents but that is all, and it will not update automatically.

Any VBA code input would be great, thanks

 

[asking]

Hi blitz
Welcome to the forum
If you are doing this only once there is no need for VBA. You can do it in conditional formatting using more than 1 rule.

However here is the code for VBA. Just change the colours to your preference.

Sub FormatConditional()
Range("D17:D300").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B17=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.599963377788629
    End With

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B17=2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399945066682943
    End With

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B17=3"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.399945066682943
    End With

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B17=4"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
    End With

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B17=""E"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With

End Sub

Cheers
Skinman