VBA code to Paste the borders If Range A:A <> ""

Question

I have wrote a code which paste the borders on Sheet1 used range whenever i make an entry and same for Sheet2. The data is cover by borders automatically.

I have been facing an error (select method of range class failed) if i apply the both codes in sheet1 and Sheet2.

If i use the code for single sheet it works.

Is there an way to merge these both codes OR any way to make it work OR to do this thing in an efficient way.

Any help will be appreciated.

Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim lngLstCol As Long, lngLstRow As Long

lngLstRow = Sheet1.UsedRange.Rows.Count
lngLstCol = Sheet1.UsedRange.Columns.Count

For Each rngCell In Range("A2:A" & lngLstRow)
    If rngCell.Value > "" Then
        r = rngCell.Row
        c = rngCell.Column
        Range(Cells(r, c), Cells(r, lngLstCol)).Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
                End With
                With Selection.Font
                .Name = "Calibri"
               .Size = 10
            End With
    End If
Next
Application.ScreenUpdating = True
End Sub

Sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
lngLstRow = Worksheets("Current Stock").UsedRange.Rows.Count
lngLstCol = Worksheets("Current Stock").UsedRange.Columns.Count

For Each rngCell In Range("A2:A" & lngLstRow)
    If rngCell.Value > "" Then
        r = rngCell.Row
        c = rngCell.Column
        Range(Cells(r, c), Cells(r, lngLstCol)).Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
                End With
                With Selection.Font
                .Name = "Calibri"
               .Size = 10
            End With
    End If
Next
Application.ScreenUpdating = True
End Sub

It's best to [avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in the first place. — BigBen, Feb 09 '21 at 15:41

JohnnieL · Accepted Answer · 2021-02-09T16:06:24.067

0

If i use the code for single sheet it works.

This might be because you are not fully qualifying ranges: If you don not qualify Cells and Range it works on the activesheet so you need to pre-qualify wuith the sheet that contains the ranges so target.parent.Cells and target.parent.range might solve your problem

Is there an way to merge these both code

Define a sub which takes a worksheet as a parameter

sub do_the_work(byref ws as worksheet)


Application.ScreenUpdating = False
lngLstRow = Worksheets("Current Stock").UsedRange.Rows.Count
lngLstCol = Worksheets("Current Stock").UsedRange.Columns.Count

For Each rngCell In ws.Range("A2:A" & lngLstRow)
    If rngCell.Value > "" Then
        r = rngCell.Row
        c = rngCell.Column
        ws.Range(ws.Cells(r, c), ws.Cells(r, lngLstCol)).Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
                End With
                With Selection.Font
                .Name = "Calibri"
               .Size = 10
            End With
    End If
Next
Application.ScreenUpdating = True

end sub

then inside the worksheet.change call

Private Sub Worksheet_Change(ByVal Target As Range)
  do_the_work target.parent
End Sub

Improvement removing select

        With ws.Range(ws.Cells(r, c), ws.Cells(r, lngLstCol))
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Font
                .Name = "Calibri"
               .Size = 10
            End With
        End With

edited Feb 09 '21 at 16:06

answered Feb 09 '21 at 15:47

JohnnieL

1,192
1
9
15

1

yes i saw your point and upvoted it already: I'm just making modification to existing code to inmplement my answer to the question not risk confusing it by making an "improvement" to the code simultaneously - but i totally agree, doing things based on select has very little upside and plenty of downside – JohnnieL Feb 09 '21 at 15:52
Thank you @JohnnieL I got it what you briefed is great help. BigBen Thank you as well for the clarification. – Feb 09 '21 at 15:57
"If you don not qualify Cells and Range it works on the activesheet" - that is not accurate. See [this answer](https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984). The implicit qualifier here is `Me`. Which by the way, `Target.Parent` can be simplified to `Me`. – BigBen Feb 09 '21 at 15:58
Whats the subtlety I'm missing here @BigBen? The reference you posted is helpful for sure. It states "Likewise, an unqualified Range() or Cells() (or Rows()/Columns()) in a standard module will reference the ActiveSheet" - so whats the subtlety thats the delta between what I wrote and what you know? thanks in advance – JohnnieL Feb 09 '21 at 16:01
1

Claiming that the implicit qualifier is always `ActiveSheet` is not accurate. In the case of a worksheet event, the implicit qualifier is `Me`. From the same sentence... "but in a sheet code module the implicit qualifier is `Me`, and will reference the corresponding worksheet." – BigBen Feb 09 '21 at 16:02

VBA code to Paste the borders If Range A:A <> ""

1 Answers1