Highlighting Specific Cells

What I would like to do is be able to show visually on the screen how to
possibly differentiate between a cell that has a number in it and a cell that
has a formula in it. Say one cell has a number 231 in it and then another
cell has a sum formula in it. I would like the cell with the number in it to
be shaded red. Is there anyway to accomplish this task.
Thanks In advance.

Select all the cells, use Format/Conditional formatting..., change "Cell
Value Is" to "Formula Is", then enter =ISNUMBER(A1), click the Format button,
choose the Patterns tab, click the color you'd like for numbers.
The A1 used above is assuming the ACTIVE Cell's address is A1. If not, then
enter the active cell's address.

Bob Umlas
Excel MVP


"mrj1701" wrote:

> What I would like to do is be able to show visually on the screen how to
> possibly differentiate between a cell that has a number in it and a cell that
> has a formula in it. Say one cell has a number 231 in it and then another
> cell has a sum formula in it. I would like the cell with the number in it to
> be shaded red. Is there anyway to accomplish this task.
> Thanks In advance.

Unfortuanatly, when i use this commad, it will highlight a cell that is a
formula because it gives a number result. What i would like to happen would
be to only have the cells that have a distinct number entered into them as
being highlighted, and if a cell has a formula, it will not be highlighted.
Thanks.

"Bob Umlas, Excel MVP" wrote:

> Select all the cells, use Format/Conditional formatting..., change "Cell
> Value Is" to "Formula Is", then enter =ISNUMBER(A1), click the Format button,
> choose the Patterns tab, click the color you'd like for numbers.
> The A1 used above is assuming the ACTIVE Cell's address is A1. If not, then
> enter the active cell's address.
>
> Bob Umlas
> Excel MVP
>
>
> "mrj1701" wrote:
>
> > What I would like to do is be able to show visually on the screen how to
> > possibly differentiate between a cell that has a number in it and a cell that
> > has a formula in it. Say one cell has a number 231 in it and then another
> > cell has a sum formula in it. I would like the cell with the number in it to
> > be shaded red. Is there anyway to accomplish this task.
> > Thanks In advance.

This is slapped together - no warranty of any kind.

Copy the code below, then right click on the tab for the sheet where you
want to do the testing/highlighting and choose View Code

Paste this code in

Public Function IsFormula(rng As Range) As Boolean
Dim str As String
Application.Volatile
IsFormula = False

On Error GoTo cleanup
str = rng.Formula

If Left(str, 1) = "=" Then
IsFormula = True
Exit Function
End If

cleanup:

End Function

Back in Excel, use conditional formatting and use FORMULA IS and use
something like

=isformula(A1)

"mrj1701" wrote:

> Unfortuanatly, when i use this commad, it will highlight a cell that is a
> formula because it gives a number result. What i would like to happen would
> be to only have the cells that have a distinct number entered into them as
> being highlighted, and if a cell has a formula, it will not be highlighted.
> Thanks.
>
> "Bob Umlas, Excel MVP" wrote:
>
> > Select all the cells, use Format/Conditional formatting..., change "Cell
> > Value Is" to "Formula Is", then enter =ISNUMBER(A1), click the Format button,
> > choose the Patterns tab, click the color you'd like for numbers.
> > The A1 used above is assuming the ACTIVE Cell's address is A1. If not, then
> > enter the active cell's address.
> >
> > Bob Umlas
> > Excel MVP
> >
> >
> > "mrj1701" wrote:
> >
> > > What I would like to do is be able to show visually on the screen how to
> > > possibly differentiate between a cell that has a number in it and a cell that
> > > has a formula in it. Say one cell has a number 231 in it and then another
> > > cell has a sum formula in it. I would like the cell with the number in it to
> > > be shaded red. Is there anyway to accomplish this task.
> > > Thanks In advance.