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!!
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?!
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 SubWe 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!