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):
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)
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:
Thanks in advance.
Regards,
Nik
Also asked here Function / VBA to avoid manually hidden cells (rows) for conditional formatting
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):
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)
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: