Conditional Formatting
 

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.

Conditional Formatting
 

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.

Conditional Formatting
 

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.

Conditional Formatting
 

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.

Conditional Formatting
 

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.

Conditional Formatting
 

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.

Conditional Formatting
 

ok...this tottally worked....amazing.
now i'm trying to conditionally format a different sheet in the same file
(same conditions...formula black font, value/text blue font) but its not
working.....does it only work on one sheet...the sheet with cell a1?

"T. Valko" wrote:

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.

Conditional Formatting
 

Which method are you using?

The UDF should work on any sheet.

If you're using the formula method change this:

=GET.CELL(48,A1)

To this:

=GET.CELL(48,!A1)

Make sure cell A1 is the active cell (is the selected cell) when you make
that change.

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
ok...this tottally worked....amazing.
now i'm trying to conditionally format a different sheet in the same file
(same conditions...formula black font, value/text blue font) but its not
working.....does it only work on one sheet...the sheet with cell a1?

"T. Valko" wrote:

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.

Conditional Formatting
 

Wow...that worked perfectly!....how do you know all this??!!
It's mind boggling how these strange formulas get excel to do exactly what
you want it to do. thank you so much for taking the time to answer my
questions.

"T. Valko" wrote:

Which method are you using?

The UDF should work on any sheet.

If you're using the formula method change this:

=GET.CELL(48,A1)

To this:

=GET.CELL(48,!A1)

Make sure cell A1 is the active cell (is the selected cell) when you make
that change.

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
ok...this tottally worked....amazing.
now i'm trying to conditionally format a different sheet in the same file
(same conditions...formula black font, value/text blue font) but its not
working.....does it only work on one sheet...the sheet with cell a1?

"T. Valko" wrote:

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.

Conditional Formatting
 

how do you know all this??!!

I have mystical powers! <vbg

Nah, actually, there's more that I don't than there is that I do know.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
Wow...that worked perfectly!....how do you know all this??!!
It's mind boggling how these strange formulas get excel to do exactly what
you want it to do. thank you so much for taking the time to answer my
questions.

"T. Valko" wrote:

Which method are you using?

The UDF should work on any sheet.

If you're using the formula method change this:

=GET.CELL(48,A1)

To this:

=GET.CELL(48,!A1)

Make sure cell A1 is the active cell (is the selected cell) when you make
that change.

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
ok...this tottally worked....amazing.
now i'm trying to conditionally format a different sheet in the same
file
(same conditions...formula black font, value/text blue font) but its
not
working.....does it only work on one sheet...the sheet with cell a1?

"T. Valko" wrote:

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.