Find a + sign in a cell, and format the cell?

[andyreloaded]

I have a spreadsheet that is mostly formulas. We may go in and append "+20" to one cell, and "-20" to another cell. Next month, when my formulas update, I don't want to forget that there is a manual adjustment nested in the cell after the formula.

Is there any way to make a conditional formula so that it will shade a cell if it contains a "+" sign or a "-" sign? Or if it contains a hard coded number?

This is a cell's contents that I would like to shade (because of the +20 on the end):

=ROUND(IFERROR(GETPIVOTDATA("Balance",iBSDATA!$A$3,"CoCd",G$1&"","FS Item",$A26),0),0)+20

Conditional formatting always gives me a headache, so any help would be greatly appreciated!

 

[Tom Urtis]

Just to clarify, what if the formula is
=A1+B1
Do you want that cell formatted too? I know it's a simplified example, but you get the idea...is it a plus sign *anywhere* in the formula, or just at the end. And if just at the end, what constitutes "end"...after the final closing parentheses, or not nested anywhere...the possibilities determine the CF logic.

 

[andyreloaded]

Just to clarify, what if the formula is
=A1+B1
Do you want that cell formatted too? I know it's a simplified example, but you get the idea...is it a plus sign *anywhere* in the formula, or just at the end. And if just at the end, what constitutes "end"...after the final closing parentheses, or not nested anywhere...the possibilities determine the CF logic.

In the spreadsheet, I was going to convert formulas like that to =Z1, and put A1+B1 in Z1, so that there would be no + or - signs in any of the summations or pivot table formulas. So any "+" or "-" at all would be shaded.

My initial thought was to use find(), but that didn't work, I guess because + is in the formula, and find is only looking at the formulas displayed results.

 

[andyreloaded]

One bump, then I'll try something else :D

 

[Tom Urtis]

Based on your description of the issue, here is how you can do this.

First, copy and paste the following UDF into a standard VBA module:

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
If IsFormula = True Then
Dim strFormula$
strFormula = rng.Formula
If InStr(strFormula, "+") > 0 Then
IsFormula = True
Else
IsFormula = False
End If
End If
End Function

Next, select the range of cells that contain the formulas you want to evaluate. For example, let's say the range of cells is from K7:M35. Select that range, and when you do, the first cell you selected (K7 in this case in most instances) will be the active cell in that range. With that range selected, and cell K7 the active cell, bring up the conditional formatting dialog box. The formula rule for this would be
=IsFormula(K7)
Then select your fill color, click OK, and the cells with formulas that have a "+" operator in them will be conditionally formatted.

I just tested this and know it works, so if you get stuck, post back.