Substitute c. for something else

[smandula]

I have a conflict between c. in Sheet1. with another c.
Is there a substitute for c.
Purpose of this macro is to find pairs of
adjacent value and highlight them in color.

Sub sequence()
With Sheets("Sheet1")
x5 = Range("AE13").Value
x6 = Range("AE14").Value
Set x = .Range("C" & x6 & ":V" & x5)
End With

For Each c In x
If c.Value = (c.Offset(0, 1).Value - 1) Then
Range(c, c.Offset(0, 1)).Select
With Selection.Interior
..Color = RGB(222, 222, 222)
..Pattern = xlSolid
End With
End If
Next
Range("A1").Select
End Sub

With Thanks

 

[JLGWhiz]

I recommend that you open the VBE and look up Life of Variables in the help
file. It might give you a better understanding of the use of c. In the
meand time you could change the code as follows:

Sub sequence()
Dim rgc As Range, x5 As Variant, x6 As Variant
With Sheets("Sheet1")
x5 = Range("AE13").Value
x6 = Range("AE14").Value
Set x = .Range("C" & x6 & ":V" & x5)
End With

For Each rgc In x
If c.Value = (rgc.Offset(0, 1).Value - 1) Then
Range(rgc, rgc.Offset(0, 1)).Select
With Selection.Interior
..Color = RGB(222, 222, 222)
..Pattern = xlSolid
End With
End If
Next
Range("A1").Select
End Sub

 

[JLGWhiz]

I missed one:

This:

If c.Value = (rgc.Offset(0, 1).Value - 1) Then

To this:

If rgc.Value = (rgc.Offset(0, 1).Value - 1) Then

 

[smandula]

Hello again,

I tried the above solution, No Luck

Here's another twist, came up with, still doesn't work
Somewhat, more simple

Sub sequence()
Dim rgc As Range
x = 1
For Each rgc In Range("B2:AX34")
If rgc.Offset(, 1).Value - rgc.Value = 1 Then
If x = 2 Then
rng = rgc.Address
End If
x = x + 1
If x = 2 Then
Range(rgc, rng.Offset(0, 1)).Select
With Selection.Interior
.Color = RGB(222, 222, 222)
.Pattern = xlSolid
End With
x = 1
End If
Else
x = 1
End If
Next
End Sub

Thank's for your reply

 

[JLGWhiz]

If x = 2 Then
rng = rgc.Address
End If
x = x + 1
If x = 2 Then
Range(rgc, rng.Offset(0, 1)).Select
With Selection.Interior


There is a logic problem here. If x is not equal to 2 in the first
occurrence then the variable rng does not get initialized, so the second If
.... Then statement will fail, becaues rng will be empty. You could write it
this way.

If x = 2 Then
rng = rgc.Address
With Range(rng, rng.Offset(0, 1))
.Color = RGB(222, 222, 222)
.Pattern = xlSolid
End With
End If
x = 1...etc.

 

[JLGWhiz]

Also, this line:

.Color = RGB(222, 222, 222)

To this:

.Interior.Color = RGB(222, 222, 222)

And I omitted the x = x + 1 when I re-wrote the
If statement, so it needs to be added back in.

 

[JLGWhiz]

With Range(rng, rng.Offset(0, 1))

Change to:

With Range(rng, rgc.Offset(0, 1))

 

[smandula]

Thanks for your effort.

There is something wrong or conflicting on sheet 1

Using Sheet 2, with a slightly different format, the macro works.

I think enough effort has been spent on this matter.

With Thanks