# Case Sensitive Conditional Formatting

• I have recently taken on the task of creating a large protected program (without macros) that automatically updates a PowerPoint presentation. I created corresponding workbooks for each sheet in the original (master) workbook. The corresponding workbooks are then used to paste object into the power point presentation. The only problem is it will not update format (text color) changes into the corresponding workbook. I have solved half the problem with conditional formatting, but I want to conditionally format the color of text to two different colors blue if Aa and Red if all CAPS. Please tell me how to differentiate between Standard word and All CAPS in the conditional formatting.

Hi Adallia.tyler, and welcome to the OzGrid!

Quote

I want to conditionally format the color of text to two different colors blue if Aa and Red if all CAPS. Please tell me how to differentiate between Standard word and All CAPS in the conditional formatting.

You do not say which version of Excel you are using. The easy answer is that, at least in Excel 2003, it is not possible directly to base a conditional format on the existing format of the text in a cell.

HOWEVER ...

If you insert the following UDF (which returns TRUE if the text is all upper case) in a new code module, you can use it to control your conditional format

Code
Function MyCase(TestText) As Boolean
MyCase = False
If TestText = UCase(TestText) Then MyCase = True
End Function

Come back if you need more help in applying this.

This formula looks at the _second_ letter in the cell and if it's a Cap it formats Condition 1 (Red), if not a cap Condition 2 (Blue).

Condition 1: "Formula Is" ="AND(CODE(MID(A1,2,1))>64,CODE(MID(A1,2,1))<97)"

Condition 2: "Formula Is" ="AND(CODE(MID(A1,2,1))>96,CODE(MID(A1,2,1))<123)"

The way it works is by converting the _second_ character to its ANSII number. Capitol A-Z are ANSII 65 to 90 and small a to z are ANSII 97 to 122. It won't work if the second character is anything but a-z or A-Z but could be adapted of course to work on any character or all the characters, etc. This is just the simplest way.

Let me know.

PS a better thread title might be "Conditonal Formatting based on Capitol Letters" ?

Quote

Please tell me how to differentiate between Standard word and All CAPS in the conditional formatting

Use the EXACT function.

• Re: Case Sensitive Conditional Formatting

Well, you're always learning something new around here.
Thanks, Dave!

• Re: Case Sensitive Conditional Formatting

Thanks for your help. I am using 2003, and I tried using the exact fx (as Dave recommended) with the fomulas (that dr recommended) and it did not work. Am I missing a step?

• Re: Case Sensitive Conditional Formatting

Does your formula look like this? "=EXACT(B10,UPPER(B10))"

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!