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

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

Sorry for the misleading.
Color which row is depend on the weekday. For instance, if today is Monday,
fill "A3:D3", "A6:D6", "A11:D11", "A20:D20", Tuesday fill "A51:D51"...etc.
The problem is, if there are some new rows must be inserted, say four new
rows insert to row 2, 5, 9 and 15, then I need to amend all the code, i.e
Monday as "A4:D4", "A8:D8", "A14:D14", "A24:D24", Tuesday to
"A55:D55"...etc, very inconvenient. Any idea?