I have a spreadsheet that has a formula in cell A1.
If a user types over it with a value, can the font change to blue so i can
see that they typed over the formula.
I need the formatting rule to apply to various cells throughout the
spreadsheet. the formulas are not identical.

Hi,

You can do this with conditional formatting using Excel 4 macro language in
the define name area, however, I will show a vba routine to do this also:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
If IsNumeric(Target) And Not Target.HasFormula And Target < "" Then
Target.Interior.ColorIndex = 6
End If
End If
End Sub

You would replace the Range("A1") reference with your range/ranges. I would
consider selecting all the cells and defining a single name and then using
that name in the Range("myName") in place of A1.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Tami" wrote:

I have a spreadsheet that has a formula in cell A1.
If a user types over it with a value, can the font change to blue so i can
see that they typed over the formula.
I need the formatting rule to apply to various cells throughout the
spreadsheet. the formulas are not identical.

first, just so you know, i'm pretty good at excel but not at the "fancy" stuff.
so i don't know what you mean by Excel 4 macro or VBA. I know how to record
macros and edit them a bit. i'm also confused as to how i do either when
i've got hundreds of cells that i'm "checking". basically i'm sending out a
model to many users and if they choos to key over my formula, i need to know,
hence the need to shade...

"Shane Devenshire" wrote:

Hi,

You can do this with conditional formatting using Excel 4 macro language in
the define name area, however, I will show a vba routine to do this also:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
If IsNumeric(Target) And Not Target.HasFormula And Target < "" Then
Target.Interior.ColorIndex = 6
End If
End If
End Sub

You would replace the Range("A1") reference with your range/ranges. I would
consider selecting all the cells and defining a single name and then using
that name in the Range("myName") in place of A1.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Tami" wrote:

I have a spreadsheet that has a formula in cell A1.
If a user types over it with a value, can the font change to blue so i can
see that they typed over the formula.
I need the formatting rule to apply to various cells throughout the
spreadsheet. the formulas are not identical.

See this:

http://groups.google.com/group/micro...cf859ecd?tvc=2

It explains how to use conditional formatting to identify cells that contain
formulas. It would be a simple matter of changing the formulas logic to
identify cells that *do not* contain formulas.

Check it out and if you need further assistance just post back.

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
first, just so you know, i'm pretty good at excel but not at the "fancy"
stuff.
so i don't know what you mean by Excel 4 macro or VBA. I know how to
record
macros and edit them a bit. i'm also confused as to how i do either when
i've got hundreds of cells that i'm "checking". basically i'm sending out
a
model to many users and if they choos to key over my formula, i need to
know,
hence the need to shade...

"Shane Devenshire" wrote:

Hi,

You can do this with conditional formatting using Excel 4 macro language
in
the define name area, however, I will show a vba routine to do this also:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
If IsNumeric(Target) And Not Target.HasFormula And Target < ""
Then
Target.Interior.ColorIndex = 6
End If
End If
End Sub

You would replace the Range("A1") reference with your range/ranges. I
would
consider selecting all the cells and defining a single name and then
using
that name in the Range("myName") in place of A1.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Tami" wrote:

I have a spreadsheet that has a formula in cell A1.
If a user types over it with a value, can the font change to blue so i
can
see that they typed over the formula.
I need the formatting rule to apply to various cells throughout the
spreadsheet. the formulas are not identical.

do you work for microsoft?

"T. Valko" wrote:

See this:

http://groups.google.com/group/micro...cf859ecd?tvc=2

It explains how to use conditional formatting to identify cells that contain
formulas. It would be a simple matter of changing the formulas logic to
identify cells that *do not* contain formulas.

Check it out and if you need further assistance just post back.

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
first, just so you know, i'm pretty good at excel but not at the "fancy"
stuff.
so i don't know what you mean by Excel 4 macro or VBA. I know how to
record
macros and edit them a bit. i'm also confused as to how i do either when
i've got hundreds of cells that i'm "checking". basically i'm sending out
a
model to many users and if they choos to key over my formula, i need to
know,
hence the need to shade...

"Shane Devenshire" wrote:

Hi,

You can do this with conditional formatting using Excel 4 macro language
in
the define name area, however, I will show a vba routine to do this also:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
If IsNumeric(Target) And Not Target.HasFormula And Target < ""
Then
Target.Interior.ColorIndex = 6
End If
End If
End Sub

You would replace the Range("A1") reference with your range/ranges. I
would
consider selecting all the cells and defining a single name and then
using
that name in the Range("myName") in place of A1.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Tami" wrote:

I have a spreadsheet that has a formula in cell A1.
If a user types over it with a value, can the font change to blue so i
can
see that they typed over the formula.
I need the formatting rule to apply to various cells throughout the
spreadsheet. the formulas are not identical.

do you work for microsoft?

No.

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
do you work for microsoft?

"T. Valko" wrote:

See this:

http://groups.google.com/group/micro...cf859ecd?tvc=2

It explains how to use conditional formatting to identify cells that
contain
formulas. It would be a simple matter of changing the formulas logic to
identify cells that *do not* contain formulas.

Check it out and if you need further assistance just post back.

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
first, just so you know, i'm pretty good at excel but not at the
"fancy"
stuff.
so i don't know what you mean by Excel 4 macro or VBA. I know how to
record
macros and edit them a bit. i'm also confused as to how i do either
when
i've got hundreds of cells that i'm "checking". basically i'm sending
out
a
model to many users and if they choos to key over my formula, i need to
know,
hence the need to shade...

"Shane Devenshire" wrote:

Hi,

You can do this with conditional formatting using Excel 4 macro
language
in
the define name area, however, I will show a vba routine to do this
also:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
If IsNumeric(Target) And Not Target.HasFormula And Target <
""
Then
Target.Interior.ColorIndex = 6
End If
End If
End Sub

You would replace the Range("A1") reference with your range/ranges. I
would
consider selecting all the cells and defining a single name and then
using
that name in the Range("myName") in place of A1.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Tami" wrote:

I have a spreadsheet that has a formula in cell A1.
If a user types over it with a value, can the font change to blue so
i
can
see that they typed over the formula.
I need the formatting rule to apply to various cells throughout the
spreadsheet. the formulas are not identical.