Prev: Conditional max
Next: Float Menu Excel 2003 vs 2007
From: Kay on 6 Jun 2010 21:58
Gary,

Great question, but the odd thing is that each product has a different range
of acceptable targets. Thus, the conditional format references cell
addresses rather than values. =AND(TRIM($J6)<>"",$J6<$M6). 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. 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.


"Kay" wrote:

> HI all,
>
> I hope someone can help me come up with code that will conditionally format
> a summary sheet(scorecard) based on the conditional formats in another
> workbook. I used Excel driven conditional formatting in my main data sheet
> which works great, but the problem is with the final scorecard.
>
> The summary is really a scorecard and only needs color indicators. So when
> I sell products in a country each product has allowable market targets.(high
> and low). The conditional formatting is easy to do in the database
> worksheet. The requirements of the scorecard are such that if any one
> product is red in the database, the metric in the scorecard is flagged with
> red , if there are no reds but there is a yellow, it flags yellow, etc.
>
> Any ideas would be very appreciated.
From: GS on 7 Jun 2010 00:05
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


 | 
Pages: 1
Prev: Conditional max
Next: Float Menu Excel 2003 vs 2007