title: VBA - Changing row shading when a column value changes even with filtered
body: <p>I'm trying to write a macro to change the colors of rows when the values in column B change. Column A will be my controlling column using 1's and 0's, i.e. column A will stay a 1 as long as column B stays the same; whenever B changes, A will flip to a 0, and so on.</p>
  
  <p>I can get it to color the rows correctly when the values in column B change, but the problem arises when I filter the data. For example: let's say I have B2-B4 set to "test1", B5-B7 set to "test2", and B8-B10 set to "test3", then I filter column B to not include "test2". Originally, the rows would be colored differently where the column values changed, but rows B2-B4 and B8-B10 are set to the same color and now they're touching since the "test2" rows are hidden.</p>
  
  <p>Here's the code I used to color the rows, but it doesn't work for filtering:</p>
  
  <pre><code>Sub ColorRows()
      Dim This As Long
      Dim Previous As Long
      Dim LastRow As Long
      Dim Color As Integer
      Dim R As Long
  
      LastRow = ActiveSheet.Range("A" &amp; Rows.Count).End(xlUp).Row
      RwColor = Array(15,0)
      Color = 0
  
      For R = 2 To LastRow
          This = Cells(R, 1).Value
          Previous = Cells(R - 1, 1).Value
          If This &lt;&gt; Previous Then Color = 1 - Color
  
          Range("A" &amp; R &amp; ":M" &amp; R).Select
          Selection.Interior.ColorIndex = RwColor(Color)
      Next R
  End Sub
  </code></pre>
  
  <p>How can I fix it so that even after filtering the rows are colored correctly when there is a change in column values?</p>