Formatting cells when Formula, (Not Result), contains certain text.

[fpt264]

I have a worksheet with many cells that contain formulas linked to other workbooks. I would like to apply conditional formatting to these cells. The formatting would be based on the contents of the formula, not the result that the formula returns.

I know how to apply conditional formatting. Where I'm stuck is coming up with a formula to identify the cells I want to format. For example what formula would select all cells containing a formula that includes the text string "Checkbook", so that formatting would be applied to the cell containing:

='C:\Users\abc42\Documents\Excel Workbooks\[Checkbook.xlsm]Balances'!$F$8

I thought I had a solution using a named range containing =FORMULATEXT(!A1), and the following formula in conditional formatting =ISNUMBER(SEARCH("Checkbook",GetFormula)). It works, but if I try to alter the formatting Excel often crashes. I suppose I could get it the way I want it and leave the formatting alone, but I would like a better solution. Excel 2016.

[dflak]

Takes a bit of VBA

Please Login or Register  to view this content.

Then set up the conditional formatting like so:
=ISNUMBER(FIND("Check",findformula(A3)))

[FDibbins]

Excel formulas (and CF) work based on what a cell displays, not what it actually contains. There is (currently) no formula to detect if another cell contains an entry or a formula

As a test of this, in A1, enter =count(B1:B100)
Suppose all cells contained a number, you would see 100 in A1
Now, in A2, enter =LEFT(A1,2)
You might expect that to return =C (the 1st 2 letters of =COUNT) but it actually returns 10 (the 1st 2 characters of 100)

[Tony Valko]

There is (currently) no formula to detect if another cell contains an entry or a formula

Excel 2013 has 2 "new" functions for this.

=ISFORMULA(A1) ... returns either TRUE or FALSE

=FORMULATEXT(A1) ... returns the formula as a text string

[FDibbins]

@ Tony, good info to know, thanks

I have 2010 here at work (2013 at home), looks like the OP also has 2010

[protonLeah]

A rather convoluted solution using GET.CELL(41):

1. For formulas to be used on the worksheet:

Create an named formula, say,
Name: CellHasFormula
refers to: =GET.CELL(41,OFFSET(INDIRECT("RC",FALSE),0,-1))

note that the: "0, -1" in the OFFSET means that this will only look at the cell to the left of the cell with the formula.

The formula can be modified, to vary the row or column offset using a named range holding the row or column offset, for example, in this case I just used MYOFFSETVAL for the column offset:

Name: Test4Formula2
refers to: =GET.CELL(41,OFFSET(INDIRECT("RC",FALSE),0,MYOFFSETVAL))
where MYOFFSETVAL refers to G1 which is currently holding "-4"

2. For Conditional formatting:

Name: CondFmtTest
refers to: =GET.CELL(41,INDIRECT("RC",FALSE))
to be used to conditionally format the cell with the cond.fmt formula

[fpt264]

Couple of days late replying, sorry. Thanks for your prompt response. I am working with your suggestion and so far it seems viable. Had to tweak it a little by changing conditional formatting from =ISNUMBER(FIND("Check",findformula(A3))) to =ISNUMBER(FIND("Check",findformula(A1))), and it's working.

Time will tell if I get any errors or crashes. Thanks for your suggestion!