VBA Conditional Formatting Issue/Bug

[Scotster]

I'm not sure if my code is wrong here or if I'm just missing something obvious.....

I'm using the following snippet to conditional format the range P4:P1000. If I manually type it all in it's absolutely fine. Using the following code it doesn't work. However, if I go into "Conditional Formatting", double click on the generated conditional format then simply hit "OK" it starts working. I've tried refreshing and everything else that I can think of. I have 10 programmatically generated conditional formats all working flawlessly, it's just this one that's the issue. It seems it might be down to using the "Match" functionality?

Set cond = Range("P4:P1000").FormatConditions.Add(xlExpression, , "=MATCH(""*CRIT*"",UPPER($W4:$X4),0)")
cond.Interior.Color = vbRed
cond.Font.Color = vbWhite
cond.Font.Bold = RgBold

if you can think of anything it would be greatly appreciated

 

[Scotster]

Changed it to.....

Set cond = Range("P4:P1000").FormatConditions.Add(xlExpression, , "=MATCH(""*CRIT*"",$W4:$X4,0)")

Seems to work fine now. Surprised that it's not case sensitive and that the "UPPER" functionality stops it from working.