Apply Conditional formatting if cell contains formula


  • Cell background has to be changed, if the cell doesn't contain formula.

    I have tried implementing it in following ways

    1. I have tried changing the cell back ground using Conditional formatting with User Defined function(to check if cell has formula or value); but, adding User Defined function degrades the performance of the template.
    2. I have tried changing background by executing VSTO code on Range Change event. But, undo stack is lost and user is unable to undo the changes
    3. I also tried using Excel 4.0 macro..please see the implementation below..

    You can use Excel 4 macro in a defined name formula to check if cell has a
    formula, using you example
    do insert>name>define, in the source box put


    and in the name box type in something descriptive like


    click OK

    Select A1:A3
    with A1 as the active cell, do format>conditional formatting, select formula
    and use


    or in a pedagogic manner


    you can actually leave out 1 if you just want one character

    now click the format button and select format and click OK twice

    But, I cannot use this method, because I get a security warning for Excel 4.0 macros when I open the template.

    Is there any other way of checking if a cell contains formula in conditional formatting?

    Thursday, July 23, 2009 7:55 AM