Loop through and ignore hidden rows

[rogered]

I have the following code which colours a cell (in column B) if another cell in a different column (G) is more than 2 from the previous cell in column G. It works great except I need it to work when I hide cells ( so that it ignores the hidden cells and just acts on the previous visible cell.) Currently It loops through all cells instead of just visible cells. The code is It loops through all cells instead of just visible cells. The code is


Private Sub CommandButton1_Click()




Dim intRows As Integer
Dim intCols As Integer
ActiveCell.SpecialCells(xlLastCell).Select
'determine how many rows and columns to work on




intRows = Selection.Row
intCols = Selection.Column

intColour = 2


For i = 2 To intRows
For j = 7 To intCols

'change colour of first cell on each row



If j = 2 Then Cells(i, 1).Interior.ColorIndex = intColour
Cells(i, j).Select
'see if value same or different, then colour cell accordingly:
If Cells(i, j).Value > Cells(i - 1, j).Value + 2 Then
intColour = (intColour + 1) Mod 55
'avoid background colour being same as text (black in this case)
If intColour = 1 Then intColour = 2
Cells(i, j - 5).Select
Selection.Interior.ColorIndex = intColour
Else
Selection.Interior.ColorIndex = Cells(i, j).Interior.ColorIndex
End If


Next j
Next i




End Sub


Thanks so much,


Rog

[westconn1]

try like

if not cells(i, j).hidden then
     If j = 2 Then Cells(i, 1).Interior.ColorIndex = intColour
     Cells(i, j).Select
     'see if value same or different, then colour cell accordingly:
      If Cells(i, j).Value > Cells(i - 1, j).Value + 2 Then
         intColour = (intColour + 1) Mod 55
         'avoid background colour being same as text (black in this case)
         If intColour = 1 Then intColour = 2
         Cells(i, j - 5).Select
         Selection.Interior.ColorIndex = intColour
         Else
         Selection.Interior.ColorIndex = Cells(i, j).Interior.ColorIndex
     End If
  end if

but you should try to avoid the use of selection and selecting, just use fully qualified address