VBA Conditional Formatting Works But Won't Apply

**daedelous00** · 12-07-2012, 08:30 PM

My conditional formatting works, but won't apply automatically.

I'm trying to apply a simple conditional format to a column (J), by testing to see if any of the values are contained within the named range "GOOD".

I am using the below VBA, and it works. The problem is it won't actually apply unless, after the script finishes, I go in to Conditional Formatting Rules Manager, open that specific formatting rule, hit OK, and then hit Apply. Then it works. Why won't it apply by itself?

(NOTE: I had to hand-jam this formula manually; hopefully there's no typos)

Formula:

Please Login or Register  to view this content.

**6StringJazzer** · 12-09-2012, 11:08 AM

I don't know why this code would not cause the conditional formatting to become effective, but I do see another problem.

"...testing to see if any of the values..." Any of what values?

SEARCH syntax is SEARCH(<search text>,<within text>). You are using a range (GOOD) as <search text>, and the cell to be formatted (J1) as <within text>. SEARCH does not iterate through the entire range GOOD to look for a match. This will search J1 for the string in only the first cell in the range GOOD. That doesn't sound like it matches your description. Even if you typed it wrong and got GOOD and J1 reversed, it would still only consider the first cell in GOOD.

Can you describe more fully what condition you are trying to test for?

**daedelous00** · 12-10-2012, 12:28 PM

6String,

I am trying to see if any of the values in range GOOD appear anywhere within the text of any cell in column J.

And I know you say it won't work, but it does seem to. I just have to go in and hit apply manually. It searches all of GOOD for a match, not just the first entry. However, if there is an alternative way to do this maybe the "apply" problem would fix itself...?

**6StringJazzer** · 12-10-2012, 06:53 PM

I have looked into this a bit more. It appears that SEARCH returns an array if the first argument is an array. This appears to be undocumented behavior, but I have heard of this sort of thing with VLOOKUP. I have attached an example [daedelous00=SEARCH example.xlsx] where you see if I use your formula and fill down, it searches J1 for the first item in GOOD, J2 for the second item in GOOD, and so forth. It does not search J1 for any item in GOOD.

I also set up conditional formatting the same way. And I was quite astonished to get the result you describe, where the formula is TRUE if any item in good is found in the cell being formatted. This also appears to be undocumented behavior.

All that being said, I still don't know why your code doesn't work, so my guru status is at risk

. Your code is exactly what the macro generator produces. I know of no magic "now do this for real" command that is missing.

**daedelous00** · 12-11-2012, 12:24 PM

OK, this might help. I've attached an example of what's going on.

Within the file there is a macro called Find_Text, which is close to what I put in this thread originally. It searches range A:A for text in the named range "COLORS" (which contains "red" and "blue"). When run, it properly highlights all of the cells containing "red," but nothing else. If you go into the Conditional Formatting Manager after you run the macro, however, and then open the rule, hit OK, and then hit Apply, it will now also highlight cells with the word "blue" in them.

Basically my macro is only looking at the first cell of the named range, for some reason, when searching A:A. It won't look for anything else but that first entry unless you go in manually and hit Apply.

I think my conditional formatting formula needs to change somehow. Maybe to a VLOOKUP of some sort? Maybe a COUNTIF? SEARCH? How else do I search a range for any of a range of other text within the cell values (that works as a conditional formatting formula)?