Conditional formatting and wildcards (to apply CF to a string of text)

[Eperluette]

Good afternoon,

I have an Excel macro that applies CF to the entire line based on the specific value entered in a cell. It works very well, but I can't get it to work for a string of text.

The line that does work is this:


Set condition1 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$O1=" & Chr(34) & "received" & Chr(34))
With condition1 'GREEN received
.Font.Color = RGB(79, 122, 50)
.Interior.Color = RGB(205, 237, 169)
End With


If a cell in column O contains the word "received" the line will turn green.

What I would like to be able to do is turn the line green if the cell contains the word "received" followed by a date, e.g. "received 13/11/2023".
As the date can change, I tried to modify the code like this by adding * at the end, but it didn't work:


Set condition1 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$O1=" & Chr(34) & "received *" & Chr(34))


I also tried "[received *]", "received
", "received " &
, "received " & [##] & "/" & [##] & "/" & [####], "received ?", "received [?]" but nothing works.

I would be grateful for any help :)

Thank you,

Eperluette

[Aflatoon]

You can use:


"=ISNUMBER(SEARCH(" & Chr(34) & "received" & Chr(34) & ",$O1))"

[Eperluette]

Oh my goodness - it worked!! Thank you so much!!! :)

[Aflatoon]

You're welcome! :)