Colour a cell based on the colour of another cell

bomalley · Feb 20th, 2002, 06:13 PM

Hi there people,

For work, I run a weekly performance report. There are summary worksheets and detail worksheets in this report. The detail worksheets are conditionally formatted based on the values, however the summary sheets (which simply show performance as red, orange or green), I can't conditionally format from the data, as they are based on a range of data. Is there any way to format a cell based on the colour of another cell?

Tom Urtis · Feb 20th, 2002, 07:08 PM

Yes there is a way but not based on conditional formatting itself, but rather on the "why" of conditional formatting. Excel does not recognize a conditionally shaded cell to be the true interior color index, because that index is subject to change per a future condition.

What you can do is insert a worksheet level change event on the Summary sheet to shade its cells a given color, depending on (I presume) the values in those cells. A Select Case structure would probably work out well in that case, as it is not limited to just 3 conditions, which a standard conditional format imposes.

Tom Urtis

bomalley · Feb 20th, 2002, 08:03 PM

Thanks for that Tom. The problem is however that the cells in the Summary Sheet don't contain values, well they do, but they are titles for the data. Eg. "Activations Completed". This cell then hyperlinks to the data on the sheet.

_________________
Cheers,
Brandon

[ This Message was edited by: bomalley on 2002-02-20 19:03 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 20th, 2002, 06:13 PM	#1
bomalley New Member Join Date: Feb 2002 Location: Auckland, New Zealand Posts: 21	Hi there people, For work, I run a weekly performance report. There are summary worksheets and detail worksheets in this report. The detail worksheets are conditionally formatted based on the values, however the summary sheets (which simply show performance as red, orange or green), I can't conditionally format from the data, as they are based on a range of data. Is there any way to format a cell based on the colour of another cell?

Feb 20th, 2002, 07:08 PM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,382	Yes there is a way but not based on conditional formatting itself, but rather on the "why" of conditional formatting. Excel does not recognize a conditionally shaded cell to be the true interior color index, because that index is subject to change per a future condition. What you can do is insert a worksheet level change event on the Summary sheet to shade its cells a given color, depending on (I presume) the values in those cells. A Select Case structure would probably work out well in that case, as it is not limited to just 3 conditions, which a standard conditional format imposes. Tom Urtis

Feb 20th, 2002, 08:03 PM	#3
bomalley New Member Join Date: Feb 2002 Location: Auckland, New Zealand Posts: 21	Thanks for that Tom. The problem is however that the cells in the Summary Sheet don't contain values, well they do, but they are titles for the data. Eg. "Activations Completed". This cell then hyperlinks to the data on the sheet. _________________ Cheers, Brandon [ This Message was edited by: bomalley on 2002-02-20 19:03 ]