clarksonneo
02-16-2011, 08:17 AM
Hi,
In my marco below, this
i = x = "ROAD" Or x = "STREET"
j = InStr(mycell, "LONDON GARDEN") > 0 Or InStr(mycell, "SMALL SITE") > 0
appears twice.
I want to amend my marco below so that the code above appears once only.
I hope that when I update "i" and "j", I don't need to update both marcos for location 1 and location 2.
In each update, I hope that I just need to update "i" and "j" once.
The picture below is the expected result I want and it is produced by my original marco.
My next reply is the admentment I did. However, it doesn't work.
Thanks
Sub Highlight_Location()
Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
For Each Y In Selection
'---------------location1
mycell = Y.Value
mycellS = Split(mycell, " ", -1)
For Each x In mycellS
i = x = "ROAD" Or x = "STREET"
j = InStr(mycell, "LONDON GARDEN") > 0 Or InStr(mycell, "SMALL SITE") > 0
If i Then
Y.Interior.ColorIndex = 6
ElseIf j Then
Y.Interior.ColorIndex = 6
End If
Next
If Y.Interior.ColorIndex = 6 Then GoTo NextY
'---------------location2
mycell1 = Y.Offset(0, 1).Value
mycellS1 = Split(mycell1, " ", -1)
For Each x In mycellS1
i = x = "ROAD" Or x = "STREET"
j = InStr(mycell1, "LONDON GARDEN") > 0 Or InStr(mycell1, "SMALL SITE") > 0
If i Then
Y.Offset(0, 1).Interior.ColorIndex = 6
ElseIf j Then
Y.Offset(0, 1).Interior.ColorIndex = 6
End If
Next
NextY:
Next
End Sub
In my marco below, this
i = x = "ROAD" Or x = "STREET"
j = InStr(mycell, "LONDON GARDEN") > 0 Or InStr(mycell, "SMALL SITE") > 0
appears twice.
I want to amend my marco below so that the code above appears once only.
I hope that when I update "i" and "j", I don't need to update both marcos for location 1 and location 2.
In each update, I hope that I just need to update "i" and "j" once.
The picture below is the expected result I want and it is produced by my original marco.
My next reply is the admentment I did. However, it doesn't work.
Thanks
Sub Highlight_Location()
Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
For Each Y In Selection
'---------------location1
mycell = Y.Value
mycellS = Split(mycell, " ", -1)
For Each x In mycellS
i = x = "ROAD" Or x = "STREET"
j = InStr(mycell, "LONDON GARDEN") > 0 Or InStr(mycell, "SMALL SITE") > 0
If i Then
Y.Interior.ColorIndex = 6
ElseIf j Then
Y.Interior.ColorIndex = 6
End If
Next
If Y.Interior.ColorIndex = 6 Then GoTo NextY
'---------------location2
mycell1 = Y.Offset(0, 1).Value
mycellS1 = Split(mycell1, " ", -1)
For Each x In mycellS1
i = x = "ROAD" Or x = "STREET"
j = InStr(mycell1, "LONDON GARDEN") > 0 Or InStr(mycell1, "SMALL SITE") > 0
If i Then
Y.Offset(0, 1).Interior.ColorIndex = 6
ElseIf j Then
Y.Offset(0, 1).Interior.ColorIndex = 6
End If
Next
NextY:
Next
End Sub