Kay presented the following explanation :
Gary,

Great question, but the odd thing is that each product has a different range
of acceptable targets.

It sounds to me like you should go with a VB solution. CF is only
useful when the sheet is designed for expected data in expected cells.

Thus, the conditional format references cell
addresses rather than values. =AND(TRIM($J6)<"",$J6<$M6).

I see BOTH addresses AND values here, and which is what I expect to see
since CF acts on cell addresses according to values as the criteria.<g

While the majority
of records could have a green status if one record has a red status, the
scorecard must show red. I was thinking maybe you could reference the color
index number. Something like...colorindex.

I don't think that's possible with CF, and so further suggests a VB
solution is in order.

In fact according to Chip Pearson's site, you can with his VB Module.
I have imported that and successfully tested on cells that I apply direct
fill color to. However, I can't get this to work with the fill color
applied as a result of conditional formatting.
Once againg, suggestions are appreciated.

Well, it sounds like you should stick with Chip's solution since it
fits your scenario. Does this change the fill in cells OR does it set
CF on the target cell[s]? (I'm not familiar with Chip's VB solution!)

As I said, CF is only useful on sheets where the expected data is put
into the expected cells. I'm not sure why you can't get this to work as
a result of CF since I can't see your file here. As long as the
criteria is what drives the CF in the form of a formula it should work.
Your sample CF condition looks like it should work fine.

regards,

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc