Home

Using formula function instead of conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-558832%22%20slang%3D%22en-US%22%3EUsing%20formula%20function%20instead%20of%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-558832%22%20slang%3D%22en-US%22%3ECan%20a%20conditional%20format%20be%20replaced%20with%20a%20if%2C%20then%20function%20containing%20cell%20formatting%20functions%3F%20If%20so%2C%20where%20can%20I%20get%20information%20and%20examples%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-558832%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eformula%20and%20function%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-560783%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20formula%20function%20instead%20of%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560783%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F257912%22%20target%3D%22_blank%22%3E%40J2-____jj%3C%2FA%3E%26nbsp%3B%2C%20formulas%20in%20Excel%20work%20with%20cells%20values%2C%20not%20with%20cells%20properties%20like%20format.%20To%20work%20with%20properties%20you%20may%20use%20VBA%20programming%2C%20or%20conditional%20formatting%2C%20or%2C%20in%20some%20extent%2C%20custom%20numbers%20formatting.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-560821%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20formula%20function%20instead%20of%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560821%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F257912%22%20target%3D%22_blank%22%3E%40J2-____jj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20develop%20your%20formulas%20and%20then%20assign%20then%20to%20a%20defined%20Name%20using%20Name%20Manager.%26nbsp%3B%20The%20formula%20should%20result%20in%20TRUE%20or%20FALSE%20when%20evaluated%20in%20each%20of%20the%20conditionally%20formatted%20cells.%26nbsp%3B%20Each%20conditional%20will%20depend%20on%20one%20such%20formula.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-560831%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20formula%20function%20instead%20of%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3Bmore%20exactly%20TRUE%20(or%20its%20numeric%20equivalent)%20or%20whatever%20else%2C%20even%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-560974%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20formula%20function%20instead%20of%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgreed%3C%2FP%3E%3CP%3ETRUE%20can%20also%20be%20any%20non-zero%20number%3C%2FP%3E%3CP%3EFALSE%20can%20also%20be%20a%20character%20string%2C%20zero%2C%20an%20error%20or%20a%20blank%20cell%3C%2FP%3E%3CP%3EHave%20I%20missed%20anything%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20thing%20that%20causes%20me%20most%20grief%20is%20the%20inability%20of%20CF%20to%20work%20with%20a%20Boolean%20array%3B%20all%20it%20seems%20to%20see%20is%20the%20value%20of%20the%20first%20item.%26nbsp%3B%20It%20also%20fails%20on%20Range%20intersection%20but%20at%20least%20that%20can%20be%20hidden%20within%20a%20defined%20name.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
J2-____jj
New Contributor
Can a conditional format be replaced with a if, then function containing cell formatting functions? If so, where can I get information and examples?
4 Replies
Highlighted

@J2-____jj , formulas in Excel work with cells values, not with cells properties like format. To work with properties you may use VBA programming, or conditional formatting, or, in some extent, custom numbers formatting.

Highlighted

@J2-____jj 

You can develop your formulas and then assign then to a defined Name using Name Manager.  The formula should result in TRUE or FALSE when evaluated in each of the conditionally formatted cells.  Each conditional will depend on one such formula. 

Highlighted

@Peter Bartholomew more exactly TRUE (or its numeric equivalent) or whatever else, even error.

Highlighted

@Sergei Baklan 

Agreed

TRUE can also be any non-zero number

FALSE can also be a character string, zero, an error or a blank cell

Have I missed anything?

 

The thing that causes me most grief is the inability of CF to work with a Boolean array; all it seems to see is the value of the first item.  It also fails on Range intersection but at least that can be hidden within a defined name.

Related Conversations
Make Share function in Edge more useful
HotCakeX in Discussions on
2 Replies
Cell Validation - Drop Down Lists and Text Formatting
JenSmith in Excel on
1 Replies
function talking to table storage
donquijote in Compute on
0 Replies
Calculated column help
gopalaraoa in SharePoint on
1 Replies