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 are: 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 are: 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 are: 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 are: 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 are: 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 are: 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 are: 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 are: 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 are: 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
> > > > > > > >
> > > > > > > >