Conditional Formatting Override

Copper Contributor

I am trying to reformat my works Excel reports. I currently have them set to auto color rows when they are populated based on their location name. We use Excel 365.

 

Example: Range: =$A$1:$C$4 -> Use the formula to determine which cells to format -> =$A1="Orlando" -> Orange 

 

Whenever something pops in with the word "Orlando" it now fills the whole row in that range.

However, in some cases, they like to manually recolor cells manually to indicate prep/shipping dates.

I have looked up a couple of sources, most say CF will override any manual input to refill a cell and only one said you can do it with code:

Function TestColor(MyRange As Range) As Boolean
    If Range(MyRange.Address).Interior.Pattern = xlNone Then TestColor = True
End Function

Then in the conditional formatting add a formula rule [change the cell ref to suit] with no formatting using this equation...

=NOT(TestColor($A$1))

 

I am either using the code wrong, highly possible, or it doesn't work in my version of Excel.

 

Bottom line, I need the ability to have CF to help with auto filling information to speed up report processing and still be able to allow people to manually change Fill Colors if they want to.

 

 

1 Reply

@Zwatson Hello! You've posted your question in the Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future.