Conditional formatting based on another cell color in excel

llama9207

New Member
Joined
May 18, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You can use this formula in CF on A1
Excel Formula:
=COUNTIF(A$2:A$5,"<>")=4
 

llama9207

New Member
Joined
May 18, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hi & welcome to MrExcel.
You can use this formula in CF on A1
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 18, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 18, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Forum statistics

Threads
1,141,412
Messages
5,706,297
Members
421,439
Latest member
JordsdoExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top