Compare values in a column
Test on Sheet1
Sub testing()
Const sh1Name = "Sheet1"
Const colA = "A"
Dim sh1 As Worksheet
Dim i As Long
Dim lastRowInColA As Long
Set sh1 = Worksheets(sh1Name)
lastRowInColA = Cells(Rows.Count, colA).End(xlUp).Row
For i = 2 To lastRowInColA
If sh1.Range(colA & i).Offset(1, 0).Value - sh1.Range(colA & i).Value 1 Then
sh1.Range(colA & i).Offset(1, 0).Interior.ColorIndex = 6
End If
Next i
End Sub
"Tendresse" wrote:
I need help with a macro that checks that cells values in column A are
increasing by one increment. In Column A i put receipts numbers. I want to be
able to check if all receipts have been recorded. for example, if column A
looks like this:
A
1 100
2 101
3 103
4 104
5 105
All values are increasing by 1, except the value in cell A3 increased by 2.
Which means that Receipt Number 102 hasn't been recorded.
I want the macro to go through cells in column A, and once it detects a gap,
the cell fill colour (cell A3 in the above example) turns red.
I'm using Excel 2003.
Many thanks
Tendresse
|