Conditional Formatting based on the color of cell

[Ray_Ray]

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!

[#2]

@Ray_Ray 

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.

[#3]

Hey@Hans Vogelaar 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()?

[#4]

@Ray_Ray 

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.

[#5]

@Hans Vogelaarahh I see what you're saying. The color in A1 would still be based off of the text in A2:A5. Lets assume that not all of the cells in A2:A5 has text. Let's say A2:A4 has "Yes" and they are colored green. A5 has no text and is colored red. Because at least one of the cells in A2:A5 is red that means A1 will be red however there is no text in A5. Let's assume A5 was manually colored red, in this instance is there a way to conditionally format A1 to pick up on A5 as red (with no text) and A1 will automatically be colored red? I have not been able to find a solution without VBA. Any thoughts?

Thank you in advance. I def appreciate your feedback.

[#6]

@Ray_Ray 

The method that I described should work.

If it doesn't for you, could you attach a sample workbook without sensitive/proprietary data?

[#7]

@Hans VogelaarI played around with what you had suggested and it worked perfectly. Appreciate it!

I had also tried looking up a solution for say if columns A2:A5 did not have text. Let's say columns A2:A5 are colored either red or green (manually maybe) without any text. In that scenario, is there a way to conditional format A1 without the colors in A2:A5 being driven by the text in each cell?

[#8]

@Ray_Ray 

If the cells A2:A5 have been colored manually, you'd need VBA to color A1.