Forum Discussion
Conditional Formatting based on the color of cell
I have a question on conditional formatting based on another cell's color. Say for example, cells A2 to A5 are grouped with A1 being the parent. A2 to A5 is either green or red and if any of those 4 cells are red then the parent, A1, needs to be red but if A2 to A5 are all green then A1 wil be green. I have not found a way without macros to automate this conditional formatting of the parent cell based on the color of the children cells. Is this even possible with or without a macro?
Thank you everyone in advance for your help with this!
7 Replies
Are A2 to A5 colored by the user? If so, you'd need VBA. Excel does not provide conditional formatting rules based on the color of cells.
If, on the other hand, you have a conditional formatting rule to color A2 to A5, you can use the condition(s) of this rule to color A1.
HeyHansVogelaar whether A2 to A5 is green or red will be driven by the text in that cell so there is a rule in the conditional formatting under the home tab in the ribbon. How would you suggest writing the logic for A1? Using or()?
Let's say that A2 to A5 are colored green if they contain Yes and red if they contain No.
Select A1.
Set its color to red (this will be the default).
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=COUNTIF(A2:A5,"Yes")=4
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.
