What criteria causes the row to be colored grey? If it is empty? If it has
certain type data? If it does not have certain type data? I know you have
case set up but I can't see it.

At any rate, to set the max range for the used range you can find the last
used row in column A with:

lr= Cells(Rows.Count, 1).End(xlUp).Row

Then set your range with:

Sheets("Sheet1").Range("A10:J" & lr).Interior.ColorIndex = xlNone

That should clear all of the cells in the used range of any shading.

"jimmy" wrote:

Hi all,

I have to write a macro to fill grey to some rows depends on the weekday,
the following code works but sometimes there will be some new rows must be
inserted into the table, it is very time consuming to amend the code (row
number). Is that a possible way for improvement? Thanks

Sheets("Sheet1").Range("A10:J64").Select
Selection.Interior.ColorIndex = xlNone
.
.
.
Case 3 To 6
Sheets("Sheet1").Range("A10,10:12,16:16,30:52").Se lect
Selection.Interior.ColorIndex = 15
Case 7
Sheets("Sheet1").Range("A10,25:26,30:38,46:46,60:6 4").Select
Selection.Interior.ColorIndex = xlNone