Hi,
I have a sheet with formulas in cells and have a macro to copy them down a certain number if the calendar date is changed on the sheet:
What I would really like to happen is when (for example) there are 20 entries (done by Count(Sheets("Log") in above code), then the formulas are copied but if I then change the calendar date and there are say 10 formulas copied down, then the formating is left behind from the previous 20.
I think to do it would be to not only "ClearContents" as in the above code but clear the border and make the background dark grey. How could I do this please?
Thanks, Fwoggie.
I have a sheet with formulas in cells and have a macro to copy them down a certain number if the calendar date is changed on the sheet:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'calendar
Dim x As Integer
If Target.NumberFormat = "dd mmm yyyy" Then
If CalendarFrm.HelpLabel.Caption <> "" Then CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height Else CalendarFrm.Height = 191
CalendarFrm.Show
Range("E5").Select
'copy formulas down in columns B16:R16
On Error GoTo NoCopy
x = Application.WorksheetFunction.Count(Sheets("Log").Range("E7:E65536"))
Application.ScreenUpdating = False
Sheets("Tax").Range("B17:R65536").ClearContents
With Sheets("Tax").Range("B16:R16")
.AutoFill Destination:=.Resize(x - 1 + 1)
End With
NoCopy:
Application.ScreenUpdating = True
End If
End Sub
I think to do it would be to not only "ClearContents" as in the above code but clear the border and make the background dark grey. How could I do this please?
Thanks, Fwoggie.