Changing a cell's color based on other cells'

Closed
Report
Posts
1
Registration date
Saturday September 25, 2010
Status
Member
Last seen
September 25, 2010
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month