Cell Data Update
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
|