On Tue, 1 Dec 2009 05:05:01 -0800, Neil Pearce
wrote:

All,

I am aware of the IS forumlae. But how would I test if a cell is a function
that calucates a number as opposed to a hard inputted number? The end goal
being to highlight any cells where a formula is overwritten using conditonal
formatting.

The forumla in question being:

=IF(NOT(B9=""),VLOOKUP(B9,'Project Overview'!$A$34:$E$52,COLUMNS('Project
Overview'!$A$33:$E$33),FALSE),0)


Appreciated as always,

Neil

I'm not sure exactly what you mean, but here are some considerations.

1. There is no native Excel function to detect formulas.
2. VBA contains tools to detect if a formula exists in a cell.
3. Conditional Formatting must refer to cells on the worksheets. In other
words, it cannot refer to a UDF.

4. If you want to use Conditional Formatting (which would be dynamic), you
could set up a cell that tests for the presence of a formula (using a UDF) in
some hidden column, and then use that cell to conditionally format the cell of
interest. For example, if you wanted to highlight cells in the range
$C$5:$C$14, you could use the IsFormula() UDF (see below) entered into, let us
say, G5:G14.

G5: =IsFormula(C5)

Then fill down to G14

Then, for conditional formatting, use this:

Select C5:C14
Formula Is: =$G5=TRUE
Format to taste

If it doesn't seem to work initially, make sure your addressing mode is
correct, and that any quotes outside the formula are eliminated.

5. You could also use a VBA Macro (sub) to highlight cells with formulas. This
would not be dynamic, however.

------------------------
To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

=====================================
Function IsFormula(rg As Range) As Boolean
IsFormula = rg.HasFormula
End Function
====================================

------------------------------------------
Or, for a Macro:

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro
by name, and <RUN.

========================================
Option Explicit
Sub HighLightFormulas()
Dim c As Range
Set c = Cells.SpecialCells(xlCellTypeFormulas)
c.Interior.Color = vbYellow
End Sub
========================================
--ron