Solved Change cell colors based on other cell color

DerbyDad03 October 15, 2019 at 03:43:51

How are you filling e.g. C4 with green? Is it via Conditional Formatting or are you manually choosing green as a fill color? In either case, which green? There are many to choose from. How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Just using fill and the default green in 365. Is there hope?!

DerbyDad03 October 15, 2019 at 06:51:55

✔ Best Answer

re: "Is there hope?!" I'm not sure yet. If you weren't using Office 365, we could write a macro that would (probably) get you what you want. Basically, the macro would monitor the sheet and when you changed a fill color, the macro would say "Oh, C4 is now green. Time to clear the fill color in C5:C11." For example, this code will clear the fill color of C5:C11 after you change the fill color of C4 to green and then hit enter or select any other cell. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("C4").Interior.Color = 5287936 Then Range("C5:C11").Interior.Color = xlNone End If End Sub We could extend that code to work with your other ranges and, if needed, include code to "refill" the ranges if you changed C4 back to blue. However, as noted at the link below, VBA macros do not work in Office 365. They can only be used with the desktop app. That leaves us with Conditional Formatting. You could write 2 CF rules that monitor C4 that basically says "If C4 is not empty, then fill C4 with green and fill C5:C11 with No Color." =$C$4<>"" If you set the font color of C4 to the same green, whatever you enter into the cell won't be seen. In other words, don't manually choose Green, let CF choose green based on the fact that C4 is no longer empty. Once you delete the contents of C4 all the cells in that range (C4:C11) would go back to whatever fill color you had originally used, e.g. blue. Does that sound like "hope"? ;-) https://support.office.com/en-us/ar... How To Post Data or Code ---> Click Here Before Posting Data or VBA Code message edited by DerbyDad03

Oh! That's clever! I will play later and let you know!