Creating Conditional Formatting with VBA

Question

I'm trying to create Conditional formatting that changes fill color based on the text in a cell, I keep getting "Argument not optional" 449 error on the bolded line.

Sub ApplyConditionalFormatting()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim cell As Range
    Dim formatRange As Range
    
    ' Set the worksheet where the data is located
    Set ws = ThisWorkbook.Worksheets("INSPECTION TEMPLATE")
    
    ' Find the last used row in column F
    lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
    
    ' Set the range in column F
    Set rng = ws.Range("F1:F" & lastRow)
    
    ' Loop through each cell in the range
    For Each cell In rng
        If cell.Value = "" Then ' Check if cell is empty
            ' Set the formatRange for applying conditional formatting (columns I to AK) for the current row
            Set formatRange = ws.Range("I" & cell.Row & ":AK" & cell.Row)
            
            ' Clear existing conditional formatting rules in the format range
            formatRange.FormatConditions.Delete
            
            ' Apply conditional formatting rules based on cell value in the same row
            With formatRange
                ' Format "ACC" with white fill color
                **.FormatConditions.Add Type:=xlTextString, String:="ACC"**
                .FormatConditions(.FormatConditions.Count).Interior.Color = RGB(255, 255, 255)
                
                ' Format "REJ" with red accent 2 fill color
                .FormatConditions.Add Type:=xlTextString, String:="REJ"
                .FormatConditions(.FormatConditions.Count).Interior.Color = RGB(230, 184, 183)
                
                ' Format blanks with orange fill color
                .FormatConditions.Add Type:=xlBlanks
                .FormatConditions(.FormatConditions.Count).Interior.Color = RGB(255, 165, 0)
            End With
        End If
    Next cell
End Sub

I want this to be automated but would it be easier to make it apply formatting to selected cells rather than using column F to get the range.

Answer 1

From using the macro recorder, it seems you're missing the TextOperator:

.FormatConditions.Add Type:=xlTextString, String:="ACC", TextOperator:=xlContains

Similarly:

.FormatConditions.Add Type:=xlTextString, String:="REJ", TextOperator:=xlContains

---

Also, I believe you want:

.FormatConditions.Add Type:=xlBlanksCondition

instead of

.FormatConditions.Add Type:=xlBlanks