Cell Data Update

[terilad]

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

 

[Jacob Skaria]

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

 

[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]

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

 

[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]

Oops thats my mistake. Change that to

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

If this post helps click Yes

 

[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]

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

 

[terilad]

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

Kind regards

Terilad