Conditional Formatting

[Tami]

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.

 

[Shane Devenshire]

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.

 

[Tami]

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...

 

[T. Valko]

See this:

http://groups.google.com/group/micr...c/browse_thread/thread/6bf323d2cf859ecd?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.

 

[Tami]

do you work for microsoft?

See this:

http://groups.google.com/group/micr...c/browse_thread/thread/6bf323d2cf859ecd?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.

 

[T. Valko]

do you work for microsoft?

No.

 

[Tami]

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]

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.

 

[Tami]

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]

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!