Determine if Cell Value is +1 or -1 Value to any Cell Values in Row Above

[Poker Joe]

I'm hoping to get some advice on what functions are best to use or a subroutine to determine if a cell value is one value more (+1) or less (-1) any of the cell values in the row above it.

If true, I simply want to identify it by placing the letter "A" in a cell, else, leave blank. The "A" or blank cells would be in the same row but adjacent to the cells that the subroutine is running to determine whether it is true.​

Below is an example of the end result I desire. Rows would be added hourly but I have a backlog of data I need to examine so I was hoping to find a solution with an autofill function or subroutine.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Test	G1	G2	G3	G4	G5		A1	A2	A3	A4	A5
2	1	2	5	8	9	12
3	2	1	5	10	11	13		A		A		A
4	3	3	7	8	15	20
5	4	1	5	9	12	13				A
6	5	4	8	10	15	17		A	A	A
7	6	1	5	9	13	16			A	A		A
8	7	18	22	23	25	26
9	8	7	12	13	16	20
10	9	8	13	14	17	21		A	A	A	A	A
11	10	1	6	10	11	23
12	11	5	12	21	22	23		A	A		A
13	12	2	7	9	15	24						A

<tbody>
</tbody>

Template (3)​

<tbody>
</tbody>

 

[njimack]

I think this is what you want, based on your explanation. Some of my results are different to yours though, e.g G4 in row 2 returns "A"...
=IF(COUNTIF($B2:$F2,B3-1)+COUNTIF($B2:$F2,B3+1),"A","")

 

[Poker Joe]

I think this is what you want, based on your explanation. Some of my results are different to yours though, e.g G4 in row 2 returns "A"...
=IF(COUNTIF($B2:$F2,B3-1)+COUNTIF($B2:$F2,B3+1),"A","")

Thank You! Your results are correct, my example was incorrect.
I just need to figure out how to put that formula into a subroutine.

 

[njimack]

Why not get VBA to directly compare each result with the row above?

 

[Poker Joe]

That's sounds great. I just can't get the lightbulb to turn on that allows me to see how to do that.

 

[njimack]

Suggest something like this...

Sub Test()

Dim LastRow As Long
Dim c As Range, d As Range
Dim TestValue As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range(Cells(3, 2), Cells(LastRow, 6))
    TestValue = c.Value
    For Each d In Range(Cells(c.Row - 1, 2), Cells(c.Row - 1, 6))
        If d = TestValue + 1 Or d = TestValue - 1 Then
            c.Offset(, 6) = "A"
            Exit For
        End If
    Next d
Next c
Application.ScreenUpdating = True
End Sub

 

[Poker Joe]

THANK YOU Neil! This helps me wrap my mind around how things need to work in VBA. For some reason I have a difficult time connecting the dots but when I have a specific routine I need and see how it needs to be written my mind absorbs it better than someone giving me a generic routine. Simple ones, no problem but the more complex they become, the faster I find myself lost in the weeds.