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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
How about?:

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

HTH
 
Upvote 0

Forum statistics

Threads
1,219,094
Messages
6,146,268
Members
450,682
Latest member
augusto89

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
Back
Top