On Aug 18, 3:06*pm, Joel wrote:
* * * * set c = *Selection.Find(What:=searchtext, After:=ActiveCell, _
* * * * * *LookIn:=xlFormulas, _
* * * * * *LookAt:=xlPart, SearchOrder:=xlByRows,
* * * * * *SearchDirection:=xlNext, _
* * * * * *MatchCase:=False, SearchFormat:=False)
* * * * if not c is nothing then
* * * * * * * * With c.Interior
* * * * * * * * * * .ColorIndex = 36
* * * * * * * * * * .Pattern = xlSolid
* * * * * * * * End With
* * * * *else
* * * * * * msgbox ("did not find " & searchtext)
* * * * *end if



"Arlette" wrote:
I need to lookup the values in column A with columns B to F, one at a
time. *I put in a code which looks up each value in each column and
highlights it in yellow. *It works great when it finds the value.
However, the problem lies when it doesnt find the value in the column.
I get a runtime error - error 91 saying "Object variable or with
variable not set". The code is given below:

Dim lastrow As Long, i As Integer, x As Integer, loc As Variant,
searchtext As Variant

Public Sub validate_names()

Sheets("Buyer Names").Select
Range("A1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row

loc = ""

For i = 2 To lastrow

* * *For x = 2 To 6

* * * * searchtext = Range("A" & i).Text
* * * * Range(searchtext).Select

* * * * Selection.Copy

* * * * Columns(x).Select

* * * * With Selection.Find(What:=searchtext, After:=ActiveCell,
LookIn:=xlFormulas, _
* * * * * * LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
* * * * * * MatchCase:=False, SearchFormat:=False).Activate

* * * * * * loc = ActiveCell.Address

* * * * * * If loc < "" Then

* * * * * * * * Range(loc).Select
* * * * * * * * With Selection.Interior
* * * * * * * * * * .ColorIndex = 36
* * * * * * * * * * .Pattern = xlSolid
* * * * * * * * End With

* * * * * * End If

* * * * *End With

* * Next

* * * * Range("A" & i).Select
* * * * With Selection.Interior
* * * * * * .ColorIndex = 36
* * * * * * .Pattern = xlSolid
* * * * End With

Next

End Sub

Please help me. *Your help is greatly appreciated.- Hide quoted text -

- Show quoted text -

Thank you so much...it really helped...