I want to write a macro that will search through a sheet and find numbers
that are hard coded, not the result of formulas, and the in that cell write a
blue 0.

The writing in blue is ancillary to the ability of finding the hard coded
numbers and zeroing them.
Is this possible? Thanks for the help?

Mike,

Here is some code

Dim rng As Range

Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
With rng
.Value = 0
.Font.ColorIndex = 5
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mike Piazza" wrote in message
...
I want to write a macro that will search through a sheet and find numbers
that are hard coded, not the result of formulas, and the in that cell
write a
blue 0.

The writing in blue is ancillary to the ability of finding the hard coded
numbers and zeroing them.
Is this possible? Thanks for the help?

This'll get text and number constants, though.

Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants, xlNumbers)

To the Original Poster. Be careful when you do this. It'll include all those
Header cells, too.


Bob Phillips wrote:

Mike,

Here is some code

Dim rng As Range

Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
With rng
.Value = 0
.Font.ColorIndex = 5
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mike Piazza" wrote in message
...
I want to write a macro that will search through a sheet and find numbers
that are hard coded, not the result of formulas, and the in that cell
write a
blue 0.

The writing in blue is ancillary to the ability of finding the hard coded
numbers and zeroing them.
Is this possible? Thanks for the help?

--

Dave Peterson

Thanks Dave,

When i use this line the macro will not include cells that have a formula
but do not reference other cells, such as = 120+56. for my purposes i would
consider that a hard key cell. Is it possible to select these ones that dont
ahve references as well?

"Dave Peterson" wrote:

This'll get text and number constants, though.

Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants, xlNumbers)

To the Original Poster. Be careful when you do this. It'll include all those
Header cells, too.


Bob Phillips wrote:

Mike,

Here is some code

Dim rng As Range

Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
With rng
.Value = 0
.Font.ColorIndex = 5
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Mike Piazza" wrote in message
...
I want to write a macro that will search through a sheet and find numbers
that are hard coded, not the result of formulas, and the in that cell
write a
blue 0.

The writing in blue is ancillary to the ability of finding the hard coded
numbers and zeroing them.
Is this possible? Thanks for the help?

--

Dave Peterson

"Mike Piazza" wrote in message
...
Thanks Dave,

When i use this line the macro will not include cells that have a formula
but do not reference other cells, such as = 120+56. for my purposes i
would
consider that a hard key cell.

Unfortunately, Excel does not.

I found somewhere that you could exclude dates by going:

For Each Rng In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
If Not IsDate(Rng.Value) Then Rng.ClearContents
Next Rng

Can I combine this with the insert blue & 0 as below:

With rng
.Value = 0
.Font.ColorIndex = 5
End With