• Conditional Formatting (color a cell)

    Author
    Topic
    #397419

    How do i color a cell based on if the Letter next to the cell is upper case or lower case? I tried

    =F4=upper(F4) but that doesn’t work

    Viewing 5 reply threads
    Author
    Replies
    • #752550

      Comparison with = is not case-sensitive. Use the EXACT function instead:

      =EXACT(F4,UPPER(F4))

    • #752551

      Comparison with = is not case-sensitive. Use the EXACT function instead:

      =EXACT(F4,UPPER(F4))

    • #752561

      I’m not sure exactly how to color the cell, but the following code will determine whether ANY letter entered is upper or lower case:

      =IF(AND(CODE(B5)>=65,CODE(B5)=97,CODE(B5)<=122),"lower",_
      "error")))

      replacing “upper” and “lower” with the commands to color the cell accordingly. It also has the added benefit of being able to determine if the character is not a valid letter at all.

    • #752562

      I’m not sure exactly how to color the cell, but the following code will determine whether ANY letter entered is upper or lower case:

      =IF(AND(CODE(B5)>=65,CODE(B5)=97,CODE(B5)<=122),"lower",_
      "error")))

      replacing “upper” and “lower” with the commands to color the cell accordingly. It also has the added benefit of being able to determine if the character is not a valid letter at all.

    • #752587

      After you do what Hans said to do, go to Format|Conditional Formatting… to set the color you want based on the condition you set.

      • #752591

        Actually, the formula I posted can be used directly in the Conditional Formatting dialog: select Formula Is in the dropdown list, and enter =EXACT(F4,UPPER(F4)) in the box next to it. then click Format… and select the font, border and shading you want. Finally click OK twice to confirm.

        • #752607

          Hans

          When using your formula, Excel does not distinguish between Empty cells and Upper Case letter.

          Lonelywind`s formula, it works fine

          Condition 1
          AND(CODE($B5)>=65,CODE($B5)= 97,CODE($B5)<= 122)

          • #752613

            Change Hans’ formula to:

            =AND(F4"",EXACT(F4,UPPER(F4)))
            

            Also, your formula only checks the first character of the cell contents, Hans’ formula checks all characters. That’s OK if it is what you want.

          • #752614

            Change Hans’ formula to:

            =AND(F4"",EXACT(F4,UPPER(F4)))
            

            Also, your formula only checks the first character of the cell contents, Hans’ formula checks all characters. That’s OK if it is what you want.

        • #752608

          Hans

          When using your formula, Excel does not distinguish between Empty cells and Upper Case letter.

          Lonelywind`s formula, it works fine

          Condition 1
          AND(CODE($B5)>=65,CODE($B5)= 97,CODE($B5)<= 122)

      • #752592

        Actually, the formula I posted can be used directly in the Conditional Formatting dialog: select Formula Is in the dropdown list, and enter =EXACT(F4,UPPER(F4)) in the box next to it. then click Format… and select the font, border and shading you want. Finally click OK twice to confirm.

    • #752588

      After you do what Hans said to do, go to Format|Conditional Formatting… to set the color you want based on the condition you set.

    Viewing 5 reply threads
    Reply To: Conditional Formatting (color a cell)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: