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.
Case Sensitive Conditional Formatting
-
-
-
Re: Paste Link (format Updates)
Hi Adallia.tyler, and welcome to the OzGrid!
QuoteI 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
CodeFunction 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.btw. I don't think your chosen thread title correctly describes your question!
-
Re: Paste Link (format Updates)
Hi Adallia and Welcome,
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" ?
-
Re: Paste Link (format Updates)
QuotePlease 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
Sounds like it.
-
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!