Forum Discussion
Conditional Formatting based on the color of cell
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.
HansVogelaarahh 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.
