Excel 2007 : Color Range of Cells Based upon Value in Another Cell

[KKSaland]

In a Pivot Table I have a column showing Current Age, 1-37, for a number of different Rooms across the top, A-ZZ, and I want to be able to look at the value in Current Age column for a particular row and have it auto fill in twelve cells of that row a certain color based upon the age.

Say if the Current Age is 1 in Row 44, I want to have the values in E44:T44 shaded yellow. If the current Age is 13 in Row 32, I want to have the values in E32:532 shaded green. How might I accomplish this?

Current Age RoomA Room B Room C, etc
Row 32 13 0 6 10 (want this shaded green because Age is 13)
Row 44 1 3 9 5 (want this shaded yellow because Age is 1)

Does this make sense? Any help would be appreciated. Thanks.

Kalli

[Fotis1991]

Hi Kalli and welcome to the forum.

For me is not clear what are you asking for!

Row 32 13 0 6 10 (want this shaded green because Age is 13)
Row 44 1 3 9 5 (want this shaded yellow because Age is 1)

IN A32, the age is 13. OK. Then in conditional formating(you mark all the cells first--E32:T32) AND TYPE THIS formula: A32=13>>choose colour>>OK

Is this, what are you looking for??? I don't think so...

But you said that you have 37 ages.
With Conditional formating, you can use only 3 conditions...so 3 colours...

I think that the best way to describe your problem, is to attach a sample workbook.

[KKSaland]

I've attached the file showing what I would like to occur using conditional formatting. Maybe it is not possible. Thanks.

K

[Fotis1991]

..So good news

We can do it using Conditional Formating.

Take a look to the attachement.

Hope to helps you.

[Haseeb Avarakkan]

Just an addition,

If you have any text In B8:B44 this will color as green, because texts are greater than numbers. So use the condition for green as,

=AND(N($B8),$B8>25)

[srobinson352]

Very nice, can I also use this to highlight a row only if one of the cells in the row are populated with a number larger than 0?

[KKSaland]

I appreciate the input. What I would like to have done is evaluate the number in each cell from B8 to B44 and color the remainder of that particular row based upon the value in the B cell.

[Haseeb Avarakkan]

Select C8:R44, then use these formulas in CF,

For green,

=AND(N($B8),$B8>25,N(C8)>0)

For Blue,

=AND($B8>=13,$B8<=25,N(C8)>0)

For Pink,

=AND(N($B8),$B8<=12,N(C8)>0)

[KKSaland]

That worked perfectly. Thank you. I was using the ColorFunction formula to count the values in the cells shaded, say, green and now that functionality seems to have disappeared after using the conditional formatting. Any idea why this would occur?

[Haseeb Avarakkan]

The Colorfunction will count only colored manually NOT via Conditional Formatting.

Try these formulas instead.

For Green,

=SUMPRODUCT((B8:B44>25)*ISNUMBER(B8:B44)*ISNUMBER(C8:R44)*(C8:R44>0))

For Blue,

=SUMPRODUCT((B8:B44>=13)*(B8:B44<=25)*ISNUMBER(C8:R44)*(C8:R44>0))

For Pink,

=SUMPRODUCT((B8:B44<=12)*ISNUMBER(C8:R44)*(C8:R44>0))

[KKSaland]

Instead of counting the number of occurrences, can you help me out with how to make it sum the values instead? I don't understand SUMPRODUCT OR ISNUMBER.

[Haseeb Avarakkan]

Green,

=SUMPRODUCT((B8:B44>25)*(C8:R44))

Blue,

=SUMPRODUCT((B8:B44>=13)*(B8:B44<=25)*(C8:R44))

Pink,

=SUMPRODUCT((B8:B44<=12)*(C8:R44))

[KKSaland]

You're the best. Thank you so very much!