Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight the exact value
Hi, the macro below is curtesy of this ng.
The only problem is that if a value to hightlight is say, a '7' it will hightlight '17' I presume that this is because there is a '7' in '17' ... Can any kind soul alter the code so it highlights the exact values in the range please as I may have to hightlight as many as 20 values in 18,000 cheers, ste Sub colourrowsin() Dim i As Long Dim Lookupvalue Dim c As Variant 'Clear the current range Range("A3:A52").Select Selection.Interior.ColorIndex = xlNone For i = 2 To 7 ' Look at values in T2 to T7 Lookupvalue = Cells(i, 20).Value '{Search A3:A52 for a match to Lookupvalue} With Range("A3:A52") Set c = .Find(Lookupvalue) If Not c Is Nothing Then '(if match then highlight the cell in range} Range(c.Address).Select With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With End If End With Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight the exact value
Change
Set c = .Find(Lookupvalue) to Set c = .FindWhat:=Lookupvalue, LookAt:=xlWhole) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ste mac" wrote in message oups.com... Hi, the macro below is curtesy of this ng. The only problem is that if a value to hightlight is say, a '7' it will hightlight '17' I presume that this is because there is a '7' in '17' ... Can any kind soul alter the code so it highlights the exact values in the range please as I may have to hightlight as many as 20 values in 18,000 cheers, ste Sub colourrowsin() Dim i As Long Dim Lookupvalue Dim c As Variant 'Clear the current range Range("A3:A52").Select Selection.Interior.ColorIndex = xlNone For i = 2 To 7 ' Look at values in T2 to T7 Lookupvalue = Cells(i, 20).Value '{Search A3:A52 for a match to Lookupvalue} With Range("A3:A52") Set c = .Find(Lookupvalue) If Not c Is Nothing Then '(if match then highlight the cell in range} Range(c.Address).Select With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With End If End With Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight the exact value
Bob, you are the man
! absolutely bang on...cheers dude.. ste |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight exact match in column and count | Excel Discussion (Misc queries) | |||
Highlight exact match in column | Excel Discussion (Misc queries) | |||
how to highlight more related cells if cell highlight | Excel Discussion (Misc queries) | |||
Routine to find exact Row matches in Col1 Col2 Col3 but exact offsetting numbers in Col4 | Excel Discussion (Misc queries) | |||
Highlight cells with ctrl-click but only un-highlight one cell | Excel Discussion (Misc queries) |