Insert Row depending of value of cell?

Posted by GUS on July 10, 2001 8:13 PM

Sorry for bother you all,

Can anyone help me and how to make a macro that can do the following.

I have made a Sheet1 where column A1 to A14 have the days of the week. And I have to insert a grey color row with the words WEEK 1 after first sunday, WEEK 2 after second sunday etc.

I been looking for to loong but I can't find anything to help me out. I am new at this and I would like to make a macro to help me to do this task.

Is there anyone who know how and can show me how to do it.

Thank You for any help you can give me.

Sincerely
GUS

Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

Posted by Franz on July 10, 2001 9:33 PM

Sub Add_Week_Row()
Dim rw As Long, nbr As Integer
rw = 8
nbr = 1
Do Until Cells(rw - 1, 1).Value = ""
With Rows(rw)
.Insert Shift:=xlDown
.Interior.ColorIndex = 40
End With
Cells(rw, 1).Value = "WEEK " & nbr
rw = rw + 8
nbr = nbr + 1
Loop
End Sub

Posted by GUS on July 11, 2001 7:02 AM

Franz thanks a lot.

Franz, One more thing how can I insert it after every sunday.
This code above insert "week 1" row between sunday and saturday. And I would like it to be after first sunday of every week.

Again Thank you very much.
GUS

Posted by Franz on July 13, 2001 2:54 AM

You stated in your original question that the days start in cell A1 which contains "Monday". Based on your follow-up, I presume that the start cell is not A1 but is in fact A2.
Change the second line of the macro from rw=8 to rw=9.

Posted by GUS on July 14, 2001 11:20 AM

You are the man Franz. Thanks a lot.......

You are always right..
GUS