Changing a cell's color based on other cells'

Closed
dba421 Posts 1 Registration date Saturday September 25, 2010 Status Member Last seen September 25, 2010 - Sep 25, 2010 at 09:34 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 25, 2010 at 09:19 PM
Hello, Please help!

I have 5 columns on Excel (A1:E5). I have conditionally formated $A:$E the following conditions:

1. If the numerical value zero is present, it will be in red font.
2. If blanks are present, it shall have no formatting.

In the sixth column, for example F2, I'd like to sum the values in A2:E5. Since there is a zero (which has a red font) in the range, I'd like to have the font of the resulting sum in red color in F2, otherwise, it shall be the automatic color (black).

As long as there is a zero (thereby condition 1 is applied) in the range of cells, the sum in another column shall also have a red font. How can I do this?

Help needed badly. :D


PS: Novice in VBA and Macros.


1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 25, 2010 at 09:19 PM
you say data is in A1 to E5 but sum is from A2 to E5. I presume that row 1(a1:E1)are column headings.

the data is something like this


hd1 hd2 hd3 hd4 hd5
27 72 38 62 68 848
84 13 0 23 64
94 15 74 28 16
20 3 83 20 44

F2 has sum of A2:E5

conditionally format F2

selecti F2
when you get conditional format window
below "condition 1"
choose "formula is"
in the small window right to this type or copy
=COUNTIF($A$2:$E$5,"0")>0
and then choose your color format.
1