SOLVED

Conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2005687%22%20slang%3D%22en-US%22%3EConditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2005687%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20guys%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20strange%20result%20from%20conditional%20formatting%2C%20i%20put%20a%20conditional%20formatting%20for%20a%20cell%20to%20have%20green%20color%20when%20cell%20value%20is%20%26gt%3B60%2C%20but%20even%20if%20i%20put%20any%20text%20it%20gives%20green%20value%20something%20is%20strange%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20a%20photo%20for%20my%20issue%2C%20it%20is%20cell%20F5%20and%20it%20contains%20IF%20function%2C%20IF%20evaluates%20to%20true%20or%20false%20so%20it%20should%20not%20be%20green%20color%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-12-21%20085033.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242000iCC920B30F92DA48A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Annotation%202020-12-21%20085033.jpg%22%20alt%3D%22Annotation%202020-12-21%20085033.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-12-21%20085102.jpg%22%20style%3D%22width%3A%20623px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242001iC81816D0A54D7960%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Annotation%202020-12-21%20085102.jpg%22%20alt%3D%22Annotation%202020-12-21%20085102.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2005687%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2005879%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2005879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F258847%22%20target%3D%22_blank%22%3E%40chahine%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20treats%20all%20text%20values%20as%20greater%20than%20all%20number%20values.%3C%2FP%3E%0A%3CP%3EChange%20the%20rule%20as%20follows%20(I%20will%20assume%20that%20B5%20is%20the%20active%20cell%20within%20the%20selection%20when%20you%20edit%20it)%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format.%3C%2FLI%3E%0A%3CLI%3EEnter%20the%20following%20formula%20in%20the%20box%3A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3DAND(ISNUMBER(B5)%2CB5%26gt%3B%3D60)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2072917%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2072917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThanks%20Hans%2C%20new%20tip%20for%20me%20that%20text%20is%20greater%20than%20number%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hello guys

 

i have strange result from conditional formatting, i put a conditional formatting for a cell to have green color when cell value is >60, but even if i put any text it gives green value something is strange

 

I attached a photo for my issue, it is cell F5 and it contains IF function, IF evaluates to true or false so it should not be green color

Annotation 2020-12-21 085033.jpgAnnotation 2020-12-21 085102.jpg

 

3 Replies
best response confirmed by chahine (Contributor)
Solution

@chahine 

Excel treats all text values as greater than all number values.

Change the rule as follows (I will assume that B5 is the active cell within the selection when you edit it):

  • Select 'Use a formula to determine which cells to format.
  • Enter the following formula in the box:

=AND(ISNUMBER(B5),B5>=60)

@Hans Vogelaar Thanks Hans, new tip for me that text is greater than number

Good idea Hans, Thanks