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
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
Highlighted

@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
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies