Prev: date & time formulas HELP !!
Next: eliminate leading quote in SAP download
From: Kay on 4 Jun 2010 14:33
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: joel on 5 Jun 2010 06:09

[You need to copy the cell with the format then use paste special using
fORMATS to paste the cell into the the sedcond workbook


To get format only:

Range("A1").Copy
Range("C1").PasteSpecial _
Paste:=xlPasteFormats


Or to get the value and the format

Range("A1").Copy
Range("C1").PasteSpecial _
Paste:=xlPasteFormats
Range("C1").PasteSpecial _
Paste:=xlPasteValues



The source and destination cells can be any cell in any workbook.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=207258

http://www.thecodecage.com/forumz

From: Kay on 5 Jun 2010 11:11
Joel,

I understand that technique, but that does not fulfill this requirement.
The summary worksheet is a much higher level report where a cell would
conditionally format if any cell in the previous worksheet has that color.
As an example. If Product A is within a certain range it might turn red, but
Product B,C,D are either yellow or green. Red must show in the summary sheet.

I hope this makes the requirements clearer and your continued input is
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: joel on 5 Jun 2010 12:36

I think you need to read the two articale Chip Pearson has under
"Conditional formating". Conditional formating contains formulas which
can refer to just the cell contents that you are copying or references
to toher cells.

depedning on the reference cells in the condional formating would
depend if you can just copy the coditional formating or would need to
modfiy the conditional formating. I assumed incorrectly that you
wouldn't need to modify the formulas.

Chip's webpage shows how to programmable work with conditional
formats.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=207258

http://www.thecodecage.com/forumz

From: Kay on 5 Jun 2010 15:49
Thanks Joel...I usually check Chip's site first, but didn't this time. I did
name the ranges so I could refer to the named range in the conditional format
formula, but I think the formula needs to be more robust.

"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.
 |  Next  |  Last
Pages: 1 2
Prev: date & time formulas HELP !!
Next: eliminate leading quote in SAP download