Clear border and change color.

Fwoggie

Board Regular
Joined
Jun 19, 2004
Messages
118
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:

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
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Perhaps use:

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

HTH
 

Fwoggie

Board Regular
Joined
Jun 19, 2004
Messages
118
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
How about?:

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

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,086
Members
412,310
Latest member
mark884
Top