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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,130
Messages
5,835,573
Members
430,367
Latest member
User800

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top