Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
SUBSTITUTE | Excel Worksheet Functions | |||
Using &Chr$(39)& as substitute for ' in VBA | Excel Discussion (Misc queries) | |||
using a substitute value | Excel Programming | |||
Substitute chr(39) | Excel Programming |