SOLVED
Home

conditional formatting with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-827899%22%20slang%3D%22en-US%22%3Econditional%20formatting%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-827899%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20conditional%20format%20set%20up%20that%20if%20D5%3DTRUE%20to%20highlight%20cells%20B18%3AD20%20yellow.%26nbsp%3B%20Now%20I%20want%20to%20be%20able%20to%20have%20those%20cell%20to%20NOT%20be%20highlighted%20when%20text%20is%20entered%20into%20them.%26nbsp%3B%20So%20basically%2C%20IF%20D5%3DTrue%2C%20turn%20cells%20yellow%2C%20then%20if%20data%20is%20entered%2C%20turn%20off%20highlight.%26nbsp%3B%20Is%20this%20possible%20as%20a%20conditional%20format%2C%20or%20do%20I%20need%20to%20utilize%20another%20method%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-827899%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormatting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-827940%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-827940%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399870%22%20target%3D%22_blank%22%3E%40kdwork%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ESomething%20like%20this%20should%20work%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3EIn%20cell%20B18%2C%20create%20the%20conditional%20formula%3A%3C%2FP%3E%3CP%3E%3DB18%26lt%3B%26gt%3B%22%22%3C%2FP%3E%3CP%3E%3CEM%3EFormat%20this%20to%20no%20highlight%2C%20applies%20to%20%3D%24B%2418%3A%24D%2420%2C%20check%20the%20box%20under%20%22Stop%20If%20True%22%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20B18%2C%20create%20the%20conditional%20formula%3A%3C%2FP%3E%3CP%3E%3D%24D%245%3DTRUE%3C%2FP%3E%3CP%3E%3CEM%3EFormat%20this%20to%20highlight%20yellow%2C%20applies%20to%20%3D%24B%2418%3A%24D%2420%2C%20do%20not%20check%20the%20box%20under%20%22Stop%20If%20True%22%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EFinally%2C%20make%20sure%20the%20format%20with%20no%20highlight%20is%20above%20the%20format%20with%20highlight%20yellow.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3CBR%20%2F%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-827981%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-827981%22%20slang%3D%22en-US%22%3E%3CP%3EAwsome!%26nbsp%3B%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-828057%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828057%22%20slang%3D%22en-US%22%3EMy%20pleasure!%3C%2FLINGO-BODY%3E
kdwork
Occasional Contributor

I have a conditional format set up that if D5=TRUE to highlight cells B18:D20 yellow.  Now I want to be able to have those cell to NOT be highlighted when text is entered into them.  So basically, IF D5=True, turn cells yellow, then if data is entered, turn off highlight.  Is this possible as a conditional format, or do I need to utilize another method?

3 Replies
Solution

Hello @kdwork,

 

Something like this should work:

In cell B18, create the conditional formula:

=B18<>""

Format this to no highlight, applies to =$B$18:$D$20, check the box under "Stop If True"

 

In cell B18, create the conditional formula:

=$D$5=TRUE

Format this to highlight yellow, applies to =$B$18:$D$20, do not check the box under "Stop If True"

 

Finally, make sure the format with no highlight is above the format with highlight yellow.

 

Hope this helps!
PReagan

Highlighted

Awsome!  Thank you.

My pleasure!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies