Lookup values in a range and highlight it. 
Author Message
 Lookup values in a range and highlight it.

Hi
I am trying to highlight cells in a range that MATCH a list of numbers in
column A1...A10. There can only be 1 match in the range per value in A1..A10

This is what I have so far but I was looking for a better way to search the
range then looping Rows then Columns looking for a match.
Thanks in advance

Sub Macro1()
    'Clear the current range
    Range("E6:X26").Select
    Selection.Interior.ColorIndex = xlNone

   For i = 1 To 10  ' Look at values in A1 to A10

   Lookupvalue = Cells(i, 1).Value

{Search E6:X26 for a match to Lookupvalue}
{if match then highlight the cell in range}

    Range(?????).Select
    With Selection.Interior
        .ColorIndex = 45
        .Pattern = xlSolid
    End With

End if
Next

End Sub



Wed, 20 Jul 2005 07:59:23 GMT  
 Lookup values in a range and highlight it.
Pal,

Sub Macro1()

  'Clear the current range
  Range("E6:X26").Select
  Selection.Interior.ColorIndex = xlNone

  For i = 1 To 10  ' Look at values in A1 to A10
    Lookupvalue = Cells(i, 1).Value
    '{Search E6:X26 for a match to Lookupvalue}
    With Range("E6:X26")
      Set c = .Find(Lookupvalue, , xlValue)
      If Not c Is Nothing Then
    '(if match then highlight the cell in range}
        Range(c.Address).Select
        With Selection.Interior
          .ColorIndex = 45
          .Pattern = xlSolid
        End With
      End If
    End With
  Next

End Sub


Quote:

> Hi
> I am trying to highlight cells in a range that MATCH a list of numbers in
> column A1...A10. There can only be 1 match in the range per value in
A1..A10

> This is what I have so far but I was looking for a better way to search
the
> range then looping Rows then Columns looking for a match.
> Thanks in advance

> Sub Macro1()
>     'Clear the current range
>     Range("E6:X26").Select
>     Selection.Interior.ColorIndex = xlNone

>    For i = 1 To 10  ' Look at values in A1 to A10

>    Lookupvalue = Cells(i, 1).Value

> {Search E6:X26 for a match to Lookupvalue}
> {if match then highlight the cell in range}

>     Range(?????).Select
>     With Selection.Interior
>         .ColorIndex = 45
>         .Pattern = xlSolid
>     End With

> End if
> Next

> End Sub



Wed, 20 Jul 2005 09:41:06 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Using VBA to select a highlighted range and paste values to another workbook

2. PASSING RANGE VALUE TO LOOKUP FUNCTION

3. vlookup formula with variable range and variable lookup values

4. Lookup Next Value In Range

5. Two-Way Lookup with range values

6. Interpolate between values in a range during Lookup

7. highlight range without knowing the range beforehand

8. LOOKUP where range to search is really a RANGE

9. Lookup Function with Two Lookup Values

10. Problem with INDEX and MATCH functions to lookup values in a unsorted lookup table


 
Powered by phpBB® Forum Software © phpBB Group