PDA

View Full Version : Loop through and ignore hidden rows



rogered
11-21-2014, 09:58 AM
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
11-21-2014, 11:43 PM
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 ifbut you should try to avoid the use of selection and selecting, just use fully qualified address