Adjust Range("B5:Z37") as your requirement..

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue

Range("B5:Z37").ClearContents
Range("B5:Z37").ClearComments
Range("B5:Z37").ClearFormats

End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Dear All,

I am looking for some help on a macro, I have a range of cells which require
to go forward 7 days, the code I have below works fine for the week
commencing
cell, the other cells are Days of week cells displayed as Monday 04 and so
on. I have moved the week comm cell data to cell N2, the other 7 cells which
require to go forward 7 days a D4,F4,H4,J4,L4,N4 and P4 these cells hold
days of week with date, displayed like Monday 04, Tuesday 05 and so on.

Here is the code:

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue
End Sub

I am also looking to put an extra code in so that all data in a range of
cells can be deleted and no fill colour, could anyone help me with this query.

Can anyone give me some help on this.

Kind Regards

Terilad