Thanks, cells code work thankyou.

I am getting an error with the following line

Range("B5:Z37").Selection.Interior.ColorIndex = xlNone

When I remove this line from the code I get no error but the cells still
remain filled with colour, any other ideas?

Error is object doesn't support this property or method

Terilad

"Jacob Skaria" wrote:

Try the below..please remove the earlier code..

Range("B5:Z37").ClearContents
Range("B5:Z37").Selection.Interior.ColorIndex = xlNone

and for the dates in D4,F4,H4,J4,L4,N4 and P4

Range("D4") = Range("D4") + 7
Range("F4") = Range("F4") + 7
and so on

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


"terilad" wrote:

Hi Jacob,

Thanks for your help, reposted as I had no response from post yesterday.
Code clears contents but it also clears all the borders aswell, I need to
keep borders on sheet, only clear cell data and return to no fill colour.

Also I mentioned too about another range of cells that need to go forward 7
days have you any ideas how to implement this into code.

Many thanks


Terilad

"Jacob Skaria" wrote:

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