Clear border and change color.

[Fwoggie]

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:

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

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.

 

[Tazguy37]

Perhaps use:

Sheets("Tax").Range("B17:R65536").Delete xlUp

HTH

 

[Fwoggie]

Tazguy,

Yes that clears everything well. Thanks.

Could you help me please to color the background to dark grey? ie: Interior.ColorIndex = 16

From x + 50 rows down and across to column K

x is the number of rows the formulas are copied down.

Thanks again Fwoggie.

 

[Tazguy37]

How about?:

Sheets("Tax").Range("B1").Offset(x - 1).Resize(50, 9).Interior.ColorIndex = 16

HTH