May 13 2019 09:35 AM
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.
May 14 2019 01:25 PM
@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.