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.

  • Re: Paste Link (format Updates)


    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.


    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: 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?

Participate now!

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