none
Apply Conditional formatting if cell contains formula

    Question

  • 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

    =GET.CELL(6,Sheet1!$A1)

    and in the name box type in something descriptive like

    FormulaIs

    click OK


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

    =LEFT(FormulaIs)="="

    or in a pedagogic manner

    =LEFT(FormulaIs,1)="="

    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

Answers