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

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

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

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

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

Oops thats my mistake. Change that to

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

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


"terilad" wrote:

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

Many thanks Jacob works now, great... just another small query before I leave
you alone, how can I add code so that when cell R2 is selected it will run
this macro, note the cell has text in it saying click to rotate shift.

Regards

Terilad

"Jacob Skaria" wrote:

Oops thats my mistake. Change that to

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

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


"terilad" wrote:

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

From VBE window, tree view. Double click 'This Workbook' and drop down to get
the below event.. It will ask for a confirmation when you select R2

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Address = Range("R2").Address Then
If MsgBox("Do you want to rotate", vbYesNo + vbDefaultButton2) < _
vbYes Then Exit Sub

'<paste your code here

End If
End Sub

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


"terilad" wrote:

Many thanks Jacob works now, great... just another small query before I leave
you alone, how can I add code so that when cell R2 is selected it will run
this macro, note the cell has text in it saying click to rotate shift.

Regards

Terilad

"Jacob Skaria" wrote:

Oops thats my mistake. Change that to

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

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


"terilad" wrote:

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

Many many thanks for your help Jacob, your a great help.

Kind regards

Terilad

"Jacob Skaria" wrote:

From VBE window, tree view. Double click 'This Workbook' and drop down to get
the below event.. It will ask for a confirmation when you select R2

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Address = Range("R2").Address Then
If MsgBox("Do you want to rotate", vbYesNo + vbDefaultButton2) < _
vbYes Then Exit Sub

'<paste your code here

End If
End Sub

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


"terilad" wrote:

Many thanks Jacob works now, great... just another small query before I leave
you alone, how can I add code so that when cell R2 is selected it will run
this macro, note the cell has text in it saying click to rotate shift.

Regards

Terilad

"Jacob Skaria" wrote:

Oops thats my mistake. Change that to

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

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


"terilad" wrote:

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