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

Perhaps consider conditional formatting. Assuming your data is in A1:A100,
select cells A2:A100, then Format/Conditional Formatting
Formula Is: =a2-a1<>1
then select Format/Pattern-Red. OK out.


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

But if you really need a macro - perhaps this will point you in the right
direction

Sub Test()
Dim rngData As Range
Dim i As Long

Set rngData = Worksheets("Sheet2").Range("A1:A5")

For i = 2 To rngData.Rows.Count
If rngData(i).Value - rngData(i - 1).Value <> 1 Then
rngData.Cells(i).Interior.ColorIndex = 53
Else: rngData.Cells(i).Interior.ColorIndex = xlNone
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
>
>

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

Hi JMB,
Thanks a lot for your reply. The macro worked perfectly. That's exactly what
i wanted. You are a champion.


"JMB" wrote:

> But if you really need a macro - perhaps this will point you in the right
> direction
>
> Sub Test()
> Dim rngData As Range
> Dim i As Long
>
> Set rngData = Worksheets("Sheet2").Range("A1:A5")
>
> For i = 2 To rngData.Rows.Count
> If rngData(i).Value - rngData(i - 1).Value <> 1 Then
> rngData.Cells(i).Interior.ColorIndex = 53
> Else: rngData.Cells(i).Interior.ColorIndex = xlNone
> 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
> >
> >