Conditional Formatting-No Formulas
 

I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

If you overwrite a formula, it's gone.

Tyro

"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

Thanks, but this is not what I had in mind. The formulas in question result
in values. We want to allow users to over-write a formula's value with a
different value (manually input), as appropriate. I then want any cell where
the formula's value has been over-written to be automatically highlighted
(say with shading) so supervisors can see where manual intervention has been
taken.

"Cimjet" wrote:

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

Thanks, but not exactly what I had in mind. I provide further details in my
response to cimjet. Thanks again.

"Tyro" wrote:

If you overwrite a formula, it's gone.

Tyro

"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select InsertModule
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn RED.


--
Biff
Microsoft Excel MVP


"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

Try something like this in Conditional Formatting:

=A5< <Your Formula

where <Your Formula is the formula in the cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"IvanM" wrote in message
...
Thanks, but this is not what I had in mind. The formulas in question
result
in values. We want to allow users to over-write a formula's value with a
different value (manually input), as appropriate. I then want any cell
where
the formula's value has been over-written to be automatically highlighted
(say with shading) so supervisors can see where manual intervention has
been
taken.

"Cimjet" wrote:

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

Copy/paste this function to a module in your workbook.

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

CFFormula is: =NOT(IsFormula(A1))


Gord Dibben MS Excel MVP

On Fri, 7 Mar 2008 14:26:02 -0800, IvanM
wrote:

Thanks, but this is not what I had in mind. The formulas in question result
in values. We want to allow users to over-write a formula's value with a
different value (manually input), as appropriate. I then want any cell where
the formula's value has been over-written to be automatically highlighted
(say with shading) so supervisors can see where manual intervention has been
taken.

"Cimjet" wrote:

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

Hi Biff:
This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could substitute
that in so I can copy the conditional formatting to other cells? I don't see
a function that can give me the address of the "current" cell without being
fed reference infomation.

Thanks again!

"T. Valko" wrote:

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select InsertModule
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn RED.


--
Biff
Microsoft Excel MVP


"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

This is a good, simple answer. I didn't consider that I don't really care
whether the cell contains a formula, but rather whether the value it holds
equals the value that the formula calculates. If someone overwrites the
formula with the same value the formula calcs, I don't really need the cell
to be shaded. Thanks

Biff has a cool solution, too, and it would be cool to see if it could also
work.

"Sandy Mann" wrote:

Try something like this in Conditional Formatting:

=A5< <Your Formula

where <Your Formula is the formula in the cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"IvanM" wrote in message
...
Thanks, but this is not what I had in mind. The formulas in question
result
in values. We want to allow users to over-write a formula's value with a
different value (manually input), as appropriate. I then want any cell
where
the formula's value has been over-written to be automatically highlighted
(say with shading) so supervisors can see where manual intervention has
been
taken.

"Cimjet" wrote:

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

You're very welcome.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"BB Ivan" wrote in message
...
This is a good, simple answer. I didn't consider that I don't really care
whether the cell contains a formula, but rather whether the value it holds
equals the value that the formula calculates. If someone overwrites the
formula with the same value the formula calcs, I don't really need the
cell
to be shaded. Thanks

Biff has a cool solution, too, and it would be cool to see if it could
also
work.

"Sandy Mann" wrote:

Try something like this in Conditional Formatting:

=A5< <Your Formula

where <Your Formula is the formula in the cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"IvanM" wrote in message
...
Thanks, but this is not what I had in mind. The formulas in question
result
in values. We want to allow users to over-write a formula's value with
a
different value (manually input), as appropriate. I then want any cell
where
the formula's value has been over-written to be automatically
highlighted
(say with shading) so supervisors can see where manual intervention has
been
taken.

"Cimjet" wrote:

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select
Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

One way:

Assume you have this formatting already applied to cell A1.
Select cell A1
Goto EditCopy
Now, select the cells where you want the formatting applied.
Goto Paste SpecialFormatsOK

--
Biff
Microsoft Excel MVP


"BB Ivan" wrote in message
...
Hi Biff:
This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional
formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could
substitute
that in so I can copy the conditional formatting to other cells? I don't
see
a function that can give me the address of the "current" cell without
being
fed reference infomation.

Thanks again!

"T. Valko" wrote:

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select InsertModule
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn
RED.


--
Biff
Microsoft Excel MVP


"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

Select all the cells prior to FormatCF

The Formula is will increment to all cells in the range.

Just make sure Excel doesn't help by adding the $ signs to make cell refs
absolute.


Gord Dibben MS Excel MVP

On Fri, 7 Mar 2008 15:39:01 -0800, BB Ivan
wrote:

This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could substitute
that in so I can copy the conditional formatting to other cells? I don't see
a function that can give me the address of the "current" cell without being
fed reference infomation.

Conditional Formatting-No Formulas
 

Great, thanks so much.

"T. Valko" wrote:

One way:

Assume you have this formatting already applied to cell A1.
Select cell A1
Goto EditCopy
Now, select the cells where you want the formatting applied.
Goto Paste SpecialFormatsOK

--
Biff
Microsoft Excel MVP


"BB Ivan" wrote in message
...
Hi Biff:
This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional
formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could
substitute
that in so I can copy the conditional formatting to other cells? I don't
see
a function that can give me the address of the "current" cell without
being
fed reference infomation.

Thanks again!

"T. Valko" wrote:

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select InsertModule
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn
RED.


--
Biff
Microsoft Excel MVP


"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks

Conditional Formatting-No Formulas
 

Great, thanks so much.

"Gord Dibben" wrote:

Select all the cells prior to FormatCF

The Formula is will increment to all cells in the range.

Just make sure Excel doesn't help by adding the $ signs to make cell refs
absolute.


Gord Dibben MS Excel MVP

On Fri, 7 Mar 2008 15:39:01 -0800, BB Ivan
wrote:

This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could substitute
that in so I can copy the conditional formatting to other cells? I don't see
a function that can give me the address of the "current" cell without being
fed reference infomation.

Conditional Formatting-No Formulas
 

I find myself constantly using the "Format Painter" (icon that looks like a
paint brush.) to copy formats.

"BB Ivan" wrote in message
...
Great, thanks so much.

"T. Valko" wrote:

One way:

Assume you have this formatting already applied to cell A1.
Select cell A1
Goto EditCopy
Now, select the cells where you want the formatting applied.
Goto Paste SpecialFormatsOK

--
Biff
Microsoft Excel MVP

Conditional Formatting-No Formulas
 

You're welcome!

--
Biff
Microsoft Excel MVP


"BB Ivan" wrote in message
...
Great, thanks so much.

"T. Valko" wrote:

One way:

Assume you have this formatting already applied to cell A1.
Select cell A1
Goto EditCopy
Now, select the cells where you want the formatting applied.
Goto Paste SpecialFormatsOK

--
Biff
Microsoft Excel MVP


"BB Ivan" wrote in message
...
Hi Biff:
This works! Thanks. A difficulty with it, however, is that in order
to
apply it to a large range of cells I'll need to set up conditional
formatting
indivually for each cell in the range (since the formatting must know
what
address to plug into cell_ref). Can you think of any way I could
substitute
that in so I can copy the conditional formatting to other cells? I
don't
see
a function that can give me the address of the "current" cell without
being
fed reference infomation.

Thanks again!

"T. Valko" wrote:

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select InsertModule
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn
RED.


--
Biff
Microsoft Excel MVP


"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks