VBA for border lines between the cells with exact / different cell value whenever the autofilter changes

NixaMKD

New Member
Joined
Jul 17, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have large spreadsheet that contains multiple data used in manufacturing area withing multiply users via SharePoint.
I want visually to improve it by segregate the various types of products using border lines whenever the autfilter changes.
My VBA is working fine if there is no filter applied on the table, but as soon as the autofilter changes, it not working.

Example without autofilter (the red solid border line is between the cells when the 'Item code' column is different and black dashed border line beween the 'Item code' column is same):
Screenshot 2023-07-13 111603.png


Example when the autofilter is applied on any of the columns (the red solid line and the black dashed border line are randomly applied + there is solid black border line as well)
Screenshot 2023-07-13 111453.png


Is there any way the VBA code to be changed / or any other solution so this to work whenever the autofilter changes or not?
At the moment the VBA that I am using:

VBA Code:
Option Explicit
Private Sub Worksheet_Calculate()
Dim lr&, i&, j&, k&, arr(), item As String
lr = Range("H100000").End(xlUp).Row
ReDim arr(1 To lr, 1 To 2)
For i = 2 To lr
    If Not Rows(i).Hidden Then
        k = k + 1: arr(k, 1) = i
        If Cells(i, "H") <> item Then arr(k, 2) = True
        item = Cells(i, "H").Value
    End If
Next
For i = 1 To k
    With Cells(arr(i, 1), "A").Resize(1, 22)
        .Borders(xlEdgeTop).LineStyle = IIf(arr(i, 2), xlContinuous, xlDot)
        .Borders(xlEdgeBottom).LineStyle = xlDot
        .Borders(xlEdgeBottom).ColorIndex = 1
        .Borders(xlEdgeTop).ColorIndex = IIf(arr(i, 2), 3, 1)
        .Borders.Weight = xlThin
    End With
Next
End Sub


Thanks in advance.

Regards,
Nik

Also asked here Function / VBA to avoid manually hidden cells (rows) for conditional formatting
 
Last edited by a moderator:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

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