Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Conditional Formatting (color a cell)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (color a cell)

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

    =EXACT(F4,UPPER(F4))

  3. #3
    3 Star Lounger
    Join Date
    May 2001
    Location
    Burlington, KS, USA
    Posts
    209
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (color a cell)

    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:<pre>=IF(AND(CODE(B5)>=65,CODE(B5)<=90),"uppe r",(IF(AND(CODE(B5)>=97,CODE(B5)<=122),"lower", _
    "error")))</pre>

    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.
    The postings on this site are my own and do not necessarily represent the position or opinion of WCNOC.

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (color a cell)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (color a cell)

    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.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (color a cell)

    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)<=90)

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

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (color a cell)

    Change Hans' formula to:

    <pre>=AND(F4<>"",EXACT(F4,UPPER(F4)))
    </pre>


    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.
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (color a cell)

    excellent, Works fine

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (color a cell)

    Thank you very much. It works great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •