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
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