Conditional Formatting Against a Formula - not it's result

I have a column with a "=IF" formula and sometimes users may need to overtype
the result when a "one off" scenario exists. I want to have Conditional
Formatting color the cell whenever the formula overtyped with a manual entry.
I tried the following formula within Conditional Formatting with the type set
to "formula is" versus "cell value is":

=IF(MID(K33,2,3)="IF","false","true")

It doesn't look at the formula within the cell only its resulting value. I
placed the above formula in another cell as a test and of course it came up
false also because it to was analyzing the cell's resulting value and not the
formula within the cell.

Is it possible to Conditional Format against a given Cell's formula and not
it's resulting value? For that matter, can you write a formula in one Cell
that attempts to analyze another Cell’s formula and not its resulting value.

Any insight would be appreciated - thank you.

Regards,
Mike

Try entering the following into your worksheets code module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$K$33" Then
If Target.HasFormula = True Then
Target.Interior.ColorIndex = -4142 'automatic
Else
Target.Interior.ColorIndex = 50 'green
End If
End If
End Sub

This will change the interior color of the cell to green whenever the
cell DOES NOT
contain a formula. You can substitute in any kind of formatting you
choose. If you need further help, gimme a holler.....

Try this:

Select Insert Name Define
In the Names in Workbook field enter CellHasFormula
In the Refers To field enter =GET.CELL(48,INDIRECT("RC",FALSE))
Select the cells to which you want to apply conditional formatting
Select the Formula Is and enter CellHasFormula
Select the type of formatting





"Mark" wrote:

>
> Try entering the following into your worksheets code module:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$K$33" Then
> If Target.HasFormula = True Then
> Target.Interior.ColorIndex = -4142 'automatic
> Else
> Target.Interior.ColorIndex = 50 'green
> End If
> End If
> End Sub
>
> This will change the interior color of the cell to green whenever the
> cell DOES NOT
> contain a formula. You can substitute in any kind of formatting you
> choose. If you need further help, gimme a holler.....
>
>

Mike

Add this Function to a module in your workbook.

Function IsFormula(Cell)
Application.Volatile
IsFormula = Cell.HasFormula
End Function

Then select the cell or cells to CF and use Formula is: =IsFormula(cellref)

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Enter the formula in CF as shown above.


Gord Dibben Excel MVP

On Thu, 10 Aug 2006 13:02:02 -0700, Mike The Newb
<[email protected]> wrote:

>I have a column with a "=IF" formula and sometimes users may need to overtype
>the result when a "one off" scenario exists. I want to have Conditional
>Formatting color the cell whenever the formula overtyped with a manual entry.
>I tried the following formula within Conditional Formatting with the type set
>to "formula is" versus "cell value is":
>
>=IF(MID(K33,2,3)="IF","false","true")
>
>It doesn't look at the formula within the cell only its resulting value. I
>placed the above formula in another cell as a test and of course it came up
>false also because it to was analyzing the cell's resulting value and not the
>formula within the cell.
>
>Is it possible to Conditional Format against a given Cell's formula and not
>it's resulting value? For that matter, can you write a formula in one Cell
>that attempts to analyze another Cell’s formula and not its resulting value.
>
>Any insight would be appreciated - thank you.
>
>Regards,
>Mike

Gord Dibben MS Excel MVP