Using VBA to Conditionally Format- IF statement

[usmananyc]

Hello all, I have a spreadsheet (A3:AE25) I would like to conditionally format based on a specific rule type. First Two row are headers that arent to be touched.

Column C houses rules anywhere from 1-20. As an example, Rule 1 requires fields D3 and F3 to have a value so I would want this highlighted in green if there is a value present. If there is a blank value or text containing the word “Required”, this field should be formatted red.

Rule 1 might also have fields that are optional like E3 and G3. I would like these to be highlighted in Orange if there is a value present or if it has the text “optional” in it. It should be highlighted in red if there is a blank.

Other fields that are not supposed to have any values values should remain blank without any formats, but if it does have a value, I would like this to have a background color of red.

So basically it would be an IF statement (looking at column C, if rule type = 1, then....) There are multiple rules that have varying fields of required/optional so a VBA script that would conditionally format these specified cells would really make things easier and reduce human error. Any help in creating a template script for two rules would be great, the rest I would be able to add myself. Thanks in Advance!!

 

[Norie]

How exactly would these 'rules' be represented?

 

[usmananyc]

I am not sure what you mean by represented exactly but it would just be a value ranging from 1-20. So if column C3 has a number 1 in it then it would imply this is rule type 1 so i would want to follow the logic for this rule type. I apologize if this sounds a bit confusing

 

[Norie]

So where/how would these 'rules' be stored?

 

[usmananyc]

I was thinking they would be stored in the VBA Logic/Script itself. They would have to be defined in an IF/OR statement.

If C3 = 1 and D3 = "Required'' or Blank then hightlight red or D3 = value then highlight in green OR
IF C4 = 2 and.... so on and so forth.

The code will get quite lengthy since each rule has a minumum of four cells to highlight but most of them are repeats with some minor differences in the cell that requires formatting.

 

[Norie]

So the 'rules' would be hard-coded?

 

[usmananyc]

Yes i suppose so. I dont see any other way for it to be done. That way when data gets pasted into the template sheet, the macro will know if the fields are populated correctly based on that specific rule.

 

[usmananyc]

Here what i have so far, however it does not seem to run when i insert the IF statement. Any advice?

Sub ConFor()
If Sheet1.Range("C3") = "1" Then




    Range("D3").Select
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(D3))>0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(D3))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False


   
    Selection.FormatConditions.Add Type:=xlTextString, String:="Required", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
End If


End Sub