Home

Conditional Formatting Override

%3CLINGO-SUB%20id%3D%22lingo-sub-564134%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Override%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564134%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20reformat%20my%20works%20Excel%20reports.%20I%20currently%20have%20them%20set%20to%20auto%20color%20rows%20when%20they%20are%20populated%20based%20on%20their%20location%20name.%20We%20use%20Excel%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20Range%3A%20%3D%24A%241%3A%24C%244%20-%26gt%3B%20Use%20the%20formula%20to%20determine%20which%20cells%20to%20format%20-%26gt%3B%20%3D%24A1%3D%22Orlando%22%20-%26gt%3B%20Orange%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhenever%20something%20pops%20in%20with%20the%20word%20%22Orlando%22%20it%20now%20fills%20the%20whole%20row%20in%20that%20range.%3C%2FP%3E%3CP%3EHowever%2C%20in%20some%20cases%2C%20they%20like%20to%20manually%20recolor%20cells%20manually%20to%20indicate%20prep%2Fshipping%20dates.%3C%2FP%3E%3CP%3EI%20have%20looked%20up%20a%20couple%20of%20sources%2C%20most%20say%20CF%20will%20override%20any%20manual%20input%20to%20refill%20a%20cell%20and%20only%20one%20said%20you%20can%20do%20it%20with%20code%3A%3C%2FP%3E%3CP%3EFunction%20TestColor(MyRange%20As%20Range)%20As%20Boolean%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20Range(MyRange.Address).Interior.Pattern%20%3D%20xlNone%20Then%20TestColor%20%3D%20True%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%20class%3D%22%22%3EThen%20in%20the%20conditional%20formatting%20add%20a%20formula%20rule%20%5Bchange%20the%26nbsp%3Bcell%20ref%20to%20suit%5D%26nbsp%3Bwith%20no%20formatting%20using%20this%20equation...%3C%2FP%3E%3CP%3E%3DNOT(TestColor(%3CSTRONG%3E%24A%241%3C%2FSTRONG%3E))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20either%20using%20the%20code%20wrong%2C%20highly%20possible%2C%20or%20it%20doesn't%20work%20in%20my%20version%20of%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBottom%20line%2C%20I%20need%20the%20ability%20to%20have%20CF%20to%20help%20with%20auto%20filling%20information%20to%20speed%20up%20report%20processing%20and%20still%20be%20able%20to%20allow%20people%20to%20manually%20change%20Fill%20Colors%20if%20they%20want%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-566565%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Override%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-566565%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341503%22%20target%3D%22_blank%22%3E%40Zwatson%3C%2FA%3E%26nbsp%3BHello!%20You've%20posted%20your%20question%20in%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FCommunity-Discussion%2Fbd-p%2FCommunityQuestions%22%20target%3D%22_blank%22%3ECommunity%20Discussion%20space%3C%2FA%3E%2C%20which%20is%20intended%20for%20discussion%20around%20the%20Tech%20Community%20website%20itself%2C%20not%20product%20questions.%20I'm%20moving%20your%20question%20to%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3EExcel%20space%3C%2FA%3E%26nbsp%3B-%20please%20post%20Excel%20questions%20here%20in%20the%20future.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Zwatson
New 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. 

Related Conversations
conditional formatting with formula
kdwork in Excel on
3 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies
Conditional Formatting values across tables
ChrisJP in Excel on
5 Replies