formula to differenciate whether a cell contains a funtion or numb
 

I wish to change the formating of a cell depending on whether it's containts
are a number which is:
a) from reference to another cell on another sheet worksheet or
b) directly entered by the user and typed over the formula

The application is a time sheet where the "planned/forcast number" is
supplied and and the user updates as they progress by over writing with
actuals numbers. I would like to have the backgound cell colour change
depending on whether the value is forcast or actual.

Any suggestions?

Cormac.

formula to differenciate whether a cell contains a funtion or numb
 

On Tue, 1 Nov 2005 09:01:06 -0800, Cormac
wrote:

I wish to change the formating of a cell depending on whether it's containts
are a number which is:
a) from reference to another cell on another sheet worksheet or
b) directly entered by the user and typed over the formula

The application is a time sheet where the "planned/forcast number" is
supplied and and the user updates as they progress by over writing with
actuals numbers. I would like to have the backgound cell colour change
depending on whether the value is forcast or actual.

Any suggestions?

Cormac.

You can do this with a combination of a VBA routine and conditional formatting.

First the VBA routine which is a UDF to determine if the cell contains a
formula:

<alt<F11 opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

==================
Function IsFormula(cell)
IsFormula = cell.HasFormula
End Function
===================

Now select the cell(s) you wish to conditionally format.

Format/Conditional Formatting

Formula Is: =IsFormula(cell) (or =IsFormula(cell) = TRUE)

or =IsFormula(cell) = FALSE

where 'cell' is the cell reference of the active cell amongst the selected
cells. Be sure to enter this address without $'s.

Depending on how you want to do the formatting.







--ron

formula to differenciate whether a cell contains a funtion or numb
 

Cormac

One method........

Use a UDF to see if cell contains formula or not then place conditional
formatting on the cell.

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

usage for CFFormula is: =ISFORMULA(cellref)


Gord Dibben Excel MVP

On Tue, 1 Nov 2005 09:01:06 -0800, Cormac
wrote:

I wish to change the formating of a cell depending on whether it's containts
are a number which is:
a) from reference to another cell on another sheet worksheet or
b) directly entered by the user and typed over the formula

The application is a time sheet where the "planned/forcast number" is
supplied and and the user updates as they progress by over writing with
actuals numbers. I would like to have the backgound cell colour change
depending on whether the value is forcast or actual.

Any suggestions?

Cormac.