Conditional formatting based on another cell color in excel

[llama9207]

Hi,
I am trying to conditional format a sum cell based on other cells colors.
I have A1 which sums A2 to A5. A2 to A5 are conditional formatted as follow: if empty red, if any number turns white.
A1 is also red and I need it to turn white only if ALL the cells below (A2 to A5) have turned white. I currently have the same rule in A1 as of in A2 to A5 = if empty red, if any number white, however with this A1 changes as soon as any of the cells (A2 to A5) turns white, meaning that I could still have red cells in A2 to A5 and A1 would have already changed to white based on only one of the cells inputs.


I have tried a couple of VBA codes, but these have to be triggered somehow. Is there a way to implement this automatically, i.e. as soon as the color of cells A2-A5 change?

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
You can use this formula in CF on A1

=COUNTIF(A$2:A$5,"<>")=4

 

[llama9207]

Hi & welcome to MrExcel.
You can use this formula in CF on A1

=COUNTIF(A$2:A$5,"<>")=4

Hi Fluff! thanks so much for your answer.
Your suggestion works perfectly if there is an input in all cells A2-A5. However, I have some cases where I wont have an input in some of the cells and they will be colored white.
What I would like to achieve is that the sum cell (A1) identifies if any of the cells below are red then A1 should stay red, for instance if A2 and A3 are red then A1 should be red. A1 should stay red if any of A2, A3, A4 or A5 is red. A1 should only turn white if A2, A3, A4 and A5 are also white.

 

[Fluff]

I don't understand, you said

A2 to A5 are conditional formatted as follow: if empty red, if any number turns white.

So how do you get an empty cell that is not red?

 

[llama9207]

I don't understand, you said

So how do you get an empty cell that is not red?

The formatting in A2 to A5 is a bit more complex, I wanted to keep it simply but I think this was misleading, sorry for this.
I am actually using 2 excel sheets in the same workbook.

Cell A1 in sheet 1 is conditionally formatted based on the value in cell A1 in sheet 2. The condition is if A1 sheet 2 is TRUE then A1 in sheet 1 turns red else it remains with no color. Cell A1 in sheet 1 is summing the range A2 to A5 in sheet 1. The range A2 to A5 in sheet 1 is blank, but conditionally formatted to turn red if the value in the same cell in the same range in sheet 2 is 1. If any value is entered in a red cell in sheet 1 (range A2 to A5) then the cell will turn white.

What I want to achieve is to have an additional condition in cell A1 in sheet 1 to check if any of the cells in the range A2 to A5 in sheet 1 are red then cell A1 remains red, else if cells in the range A2 to A5 sheet 1 have turned white because a value was entered then A1 also turns white. In other words, A1 should only turn white if all cells in the range A2 to A5 in sheet 1 are also white.

Thanks for your help!

 

[Fluff]

Conditional formatting cannot "see" the format of a cell, so you will need to use a formula to determine if A1 should be red or not.
From what I understand A1 should be red unless all cells in A2:A5 on that sheet have a value. In which case you can use the formula I suggested to turn the cell white.

 

[llama9207]

Conditional formatting cannot "see" the format of a cell, so you will need to use a formula to determine if A1 should be red or not.
From what I understand A1 should be red unless all cells in A2:A5 on that sheet have a value. In which case you can use the formula I suggested to turn the cell white.

Ok, thanks for having a look.

 

[Fluff]

You're welcome.