I am trying to shade every other group of visiable cells.

Each row of my data contains information on a given Order and there can be multiple rows for each order, e.g. Order 1 many have 3 rows while order 2 may have 1 row, etc. The data is sorted by Order Number so all rows for a given order are contiguous.

I have shaded each group vis a helper column (AS) containing the following formula: =IF(ROW()=2,TRUE,IF(A2=A1,AS1,NOT(AS1))) which results in every other Order group being either TRUE or False. Then I use conditional formatting to shade every "TRUE" row.

This works until I begin filtering my data and then I can end up with either two shaded or to unshaded groups next to each other.

I think what I'm looking for is a VBA function that will compare a cell with previous VISIBLE cell and will return TRUE or FALSE if the match or not.

Any help will be much appreciated.

CodePudding user response：

As I said in the comments, there's almost surely a better way to do what you're trying as a whole with your spreadsheet (a table!). However, if you really wanted a VBA custom formula to test if a cell is hidden or not you could use this...

Function isHiddenRow(aRange As Range) As Boolean

    isHiddenRow = aRange.EntireRow.Hidden

End Function

There's some possibilities this formula assumes:

• Only one cell.
• Filtering impact of recalculations.

CodePudding user response：

you can use this code that shades every other row

Sub ShadeThem()

    Dim okShade As Boolean
    
    Dim r As Range
        For Each r In Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible)
            If okShade Then
                r.EntireRow.Interior.Color = vbRed
                okShade = False
            Else
                okShade = True
            End If
        Next
        
End Sub

I assumed your filtered data affect column A from row 1 downwards Did they not, just change "A1" and Cells(Rows.Count, "A") to affect the needed column

In order to have it run at every new filtering, you could:

• add a helper cell which counts the number of visible rows =SUBTOTAL(103;A1:A1000) this will trigger the Worksheet_Calculate event at every filtering

• add the Worksheet_Calculate event hander in the relevant sheet code pane

   Private Sub Worksheet_Calculate()
  
       ShadeThem
  
   End Sub