Ray,

In your code, you've Deleted the cell in question, so then the
With statement is pointing at Nothing when you try to use the
NumberFormat property. Rather than use Delete, you probably want
to use Clear.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ray Batig" wrote in message
nk.net...
Greetings,

When I run the macro below, everything works fine until I get
to the .
NumberFormat line. Then I get an error message that says Object
required.
What I am trying to do is remove any colors and erase the cells
contents in
the range, and then set the format to numeric with commas and
no zeros.

How can I make this work?

Thanks in advance for your help!

Ray

Sub ResetSheet()
Dim bclro As Long

Application.ScreenUpdating = False ' turn off screen updating
bclro = Cells(65536, 1).End(xlUp).row
With Range(Cells(30, 1), Cells(bclro, 55))
.Interior.ColorIndex = xlNone
.Delete
.NumberFormat = "#,##0"
End With