ExcelBanter - Substitute c. for something else

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Substitute c. for something else (https://www.excelbanter.com/excel-programming/441851-substitute-c-something-else.html)

smandula

Substitute c. for something else

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[_2_]

Substitute c. for something else

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

"smandula" wrote in message
...
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[_2_]

Substitute c. for something else

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

"JLGWhiz" wrote in message
...
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

"smandula" wrote in message
...
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

smandula

Substitute c. for something else

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[_2_]

Substitute c. for something else

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.

"smandula" wrote in message
...
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[_2_]

Substitute c. for something else

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" wrote in message
...
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.

"smandula" wrote in message
...
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[_2_]

Substitute c. for something else

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

Change to:

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

"JLGWhiz" wrote in message
...
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.

"smandula" wrote in message
...
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

smandula

Substitute c. for something else

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

All times are GMT +1. The time now is 12:08 AM.