Solved Change cell colors based on other cell color

October 14, 2019 at 16:52:27
Specs: Windows 7
Hi!

I have a spread sheet that I want to make all one color, lets say light blue. If I fill C4 Green, I want C5 to C11s fill to be removed. Same D4, remove fill from D5 to D11. E4, remove fill from E5 to E11. That patteren goes across to O4. Then same again for C12, remove fill from C13 to C19 etc etc.

Can't workout whether thats a tricky as or not!!


See More: Change cell colors based on other cell color

Reply ↓  Report •

#1
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


Reply ↓  Report •

#2
October 15, 2019 at 03:54:09
Just using fill and the default green in 365. Is there hope?!

Reply ↓  Report •

#3
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


Reply ↓  Report •

Related Solutions

#4
October 15, 2019 at 13:18:52
Oh! That's clever! I will play later and let you know!

Reply ↓  Report •

Ask Question