Hi, I have a column G of numbers 1 to x. I then have another macro which hides certain rows so that instead of 1,2,3,4 you may get 1,2,3,16. i then want to identify when there is a break in the numbers e.g. 4,5,6,7 15. (a break of more than 2) which should then colour a corresponding cell in column B. Currently it loops every cell i think and therefore doesn't recognise the jump between numbersThanks for your help
Private Sub CommandButton1_Click()[/SIZE]
Dim intRows As Integer
Dim intCols As Integer
Range("G37").Select
'would like to use ActiveCell.SpecialCells(xlLastCell).Select but this often changes to a random cell instead of the last visible cell in column G
'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
If Not Cells(i, j).EntireRow.Hidden And Not Cells(i, j).EntireColumn.Hidden Then
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).Interior.ColorIndex = intColour ' no selection needed to do this
Else
Selection.Interior.ColorIndex = Cells(i, j).Interior.ColorIndex ' this still needs to be solved, not sure of your intent
End If
End If
Next j
Next i
End Sub
Bookmarks