I wrote a macro to place a semi transparent box (Rectangle 1) around the previous visible row and (Rectangle 2) around the next visible row of my active cell as well as many other things. My code runs as expected until I use the autofilter function. When I use the autofilter, one of two things happen, 1) If Rectangle 1 & 2 are in rows that remain visible after the filter is enabled the code executes as is expected by placing Rectangle 1 in the previous visible row and placing Rectangle 2 in the next visible row.
2) If either Rectangle 1 or 2 are in a row that will be hidden after the filter is enabled, the affected Rectangles appear to get locked into the hidden area and thus are no longer visible and free to move to the previous and next visible rows
I have tested my code to ensure that the proper row offsets are being passed and it appears that they are. Is there a way to either keep the Rectangles from getting locked into hidden cells or if they do get locked into hidden cells, break them out again so they are able to be seen and move appropriately? I have attached my code below as well as a dummy version of my spread sheet.
Bookmarks