# Thread: Conditional Formatting (color a cell)

1. ## 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. ## Re: Conditional Formatting (color a cell)

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

=EXACT(F4,UPPER(F4))

3. ## 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.

4. ## 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. ## 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. ## 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. ## 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.

8. ## Re: Conditional Formatting (color a cell)

excellent, Works fine

9. ## 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
•