VBA Conditional Formatting, Fill Color if range contains specific text-CodePudding

Hello and thanks in advance for the assistance,

It's been a while since I've posted, used to work with Google script, now I'm on to excel VBA.

I feel very new again as some things translate and others I just don't understand yet.

Anyways, I'm trying to automate a spreadsheet that needs to get formatted from an automatically generated report and I'm almost there, but I can't seem to wrap my head around any of the conditional formatting in VBA.

Here's what I'm trying to do: Range 3:3 on Sheet "Sheet2" to be highlighted RBG (221, 235, 247) if cells contain "holiday" "vacation" "sick" or "birthday"

Ive written some code a bunch of different ways, but the only thing I could get sort of working was this

With Range("3:3").FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:="holiday")
    .Bold = True
End With

This seemed to bold the cells with "holiday" inside, but I couldnt find the code for Fill Color (tried: With .Interior.Color = RGB(221, 235, 247)) and I also need multiple text string as stated above AND it needs to NOT use active sheet but a named sheet. And yes I know I can easily do this with conditional formatting, but I don't want to have to apply it to every new workbook created on top of other formatting needed when I can hit the macro button once and be done with it.

Thanks again for any assistance and please let me know if there any clarity needed, it's been a while since I've talked about this stuff.

CodePudding user response：

Try this:

Sub Tester()
    
    Const FRM As String = "=NOT(AND(ISERROR(FIND(""holiday"",<addr>)),ISERROR(" & _
    "FIND(""vacation"",<addr>)),ISERROR(FIND(""sick"",<addr>)),ISERROR(FIND(""birthday"",<addr>))))"
    Dim rng As Range, addr As String
    
    Set rng = ActiveSheet.Rows(3)             'or some other sheet...
    addr = rng.Cells(1).Address(False, False) 'address of first cell
    
    With rng.FormatConditions.Add(Type:=xlExpression, _
                                  Formula1:=Replace(FRM, "<addr>", addr))
        .SetFirstPriority
        .Font.Bold = True
        .Interior.Color = RGB(221, 235, 247)
        .StopIfTrue = False
    End With
     
End Sub

Or you can use a simpler formula by following this: https://www.extendoffice.com/documents/excel/3793-excel-conditional-formatting-search-for-multiple-words.html

CodePudding user response：

Excel Tested

Sub colorMePlease()

'Give a nickname to your sheet:
Dim x As Worksheet
Set x = Sheets("Sheet2")

'Set a variable with the key works you want to find
Dim keyWords As String
keyWords = "holiday-vacation-sick-birthday"

'Just a counter
Dim c As Long

'For loop from row 3 (where your column starts to finding the last row of your column)
For c = 3 To x.Cells(Rows.Count, 3).End(xlUp).Row
    'Check if the cell content (in lower case) is in your keyWords variable
    'The InStr function starts looking at position 1 and if if finds the
    'string you're looking, it returns a value larger than 0
    If InStr(1, keyWords, LCase(x.Cells(c, 3).Value))  0 Then
        x.Cells(c, 3).Interior.Color = RGB(221, 235, 247)
    End If
Next c
End Sub