Function / VBA to avoid manually hidden cells (rows) for conditional formatting
Hello,
I have large spreadsheet that contains multiple data. Want to apply conditional formatting on the table so visually can segregate the various products for each line (sort A to Z on column D).
The function (column H) / conditional formatting rule work fine if the data is just sorted, but as soon as it's filtered (in this case want to be filtered by each line (column E), the function (column H) is calculating the hidden cells and the conditional formatting is wrong.
Is there any way this to be solved with function or maybe VBA so when the data is filtered / the rows manually hidden the function / condittional formatting to segregate the table?
Re: Function / VBA to avoid manually hidden cells (rows) for conditional formatting
My solution is to use VBA to trigger an event whenever the autofilter changes and update the border style and color to differentiate the items in column D.
Unfortunately, to trigger the autofilter, we need to use the Calculate event.
However, if there are no cells containing formulas in the worksheet, this event will not run when the filter is changed.
In the example file, I temporarily added a formula =1+1 to cell J1. If your actual file already has at least one formula, you can skip this step.
First, remove all existing conditional formatting formulas, also the helper column with true/false, and then paste the following code into the worksheet module:
PHP Code:
Private Sub Worksheet_Calculate() Dim lr&, i&, j&, arr(), item As String lr = Range("A1000").End(xlUp).Row ReDim arr(1 To lr - 1, 1 To 2) For i = 2 To lr If Not Rows(i).Hidden Then k = k + 1: arr(k, 1) = i If Cells(i, "D") <> item Then arr(k, 2) = True item = Cells(i, "D").Value End If Next For i = 1 To k With Cells(arr(i, 1), "A").Resize(1, 6) .Borders(xlEdgeTop).LineStyle = IIf(arr(i, 2), xlContinuous, xlDot) .Borders(xlEdgeBottom).LineStyle = xlDot .Borders(xlEdgeTop).ColorIndex = IIf(arr(i, 2), 3, 1) .Borders(xlEdgeBottom).ColorIndex = 1 .Borders.Weight = xlThin End With Next End Sub
Re: Function / VBA to avoid manually hidden cells (rows) for conditional formatting
Hi bebo021999,
This is very high level for me and I can't adjust the vba code to work on my actual file .
Attached is the similar form as the original - the file has a lot of formulas, so the calculation even is fine.
Can you please help me to adjust the VBA to trigger an event whenever the autofilter changes and update the border style and color to differentiate the items in column H.
Also, I have noticed when the table is filtered / autofilter, the red borderline (the one that is dividing the product) is not visible between the hidden files, can you please look on that as well, because want always they to be divided whenever is auto filtered.
Re: Function / VBA to avoid manually hidden cells (rows) for conditional formatting
Hi bebo021999,
The code fit good on my original file, but still is not working properly.
When the filter is not applied works all good, but when is the filter is applied is not.
Here is example of non-filtered (the red solid border line is dividing the rows when the 'Item code' column is different and black dashed border line when the 'Item code' column is same): Screenshot 2023-07-13 111603.png
Here is example of filtered (the red solid line and black dashed border line are randomly applied + here are a lot of solid black border line as well): Screenshot 2023-07-13 111453.png
Can this be fixed?
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
Posts
80,916
Re: Function / VBA to avoid manually hidden cells (rows) for conditional formatting
Administrative Note:
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.
Ali Enthusiastic self-taught user of MS Excel who's always learning! Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish. Forum Rules (updated August 2023): please read them here.
Re: Function / VBA to avoid manually hidden cells (rows) for conditional formatting
Hi AliGW,
I truly understand the frustration, please accept my apology!
I have posted new thread, this time properly and my intention is to look for more experts to solve my issue on my spreadsheet.
LinkBack URL
About LinkBacks
Register To Reply
.
Bookmarks