Sub LookForExactMatches()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim rng4 As Range, rng5 As Range
Dim cell As Range, rng2A As Range
Dim icol As Long
With Worksheets(1)
Set rng = .Range(.Cells(4, 4), _
.Cells(4, 256).End(xlToLeft))
Set rng2A = rng.Find(What:="s", After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If rng2A Is Nothing Then
MsgBox "Second Column Not found"
Exit Sub
End If
icol = rng2A.Column
Set rng1 = .Range(.Cells(6, 3), _
.Cells(6, 3).End(xlDown))
Set rng2 = .Range(.Cells(6, icol), _
.Cells(6, icol).End(xlDown))
Set rng3 = Union(rng1, rng2)
rng3.Interior.ColorIndex = xlNone
End With
With Worksheets(2)
Set rng4 = .Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
End With
For Each cell In rng3
Set rng5 = rng4.Find( _
What:=cell.Value, _
After:=rng4(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If rng5 Is Nothing Then
cell.Interior.ColorIndex = 6
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"Celt" wrote:


TIA for any help provided.

I have searched and found lots of examples of how to "find" a cell with
a macro, but need to take it a step further.

My workbook has 2 sheets. On the first are two lists containing
alphanumeric input. The location of the first list will always be
fixed (ie. input will always start at cell C6). However, the second
column, could be any number of columns to the right of the
first...input always starting with row 6. The 2nd sheet contains the
master list... located in cells A4:lrow.

The "headers" of both these columns are the same, the word "Code" (on
row 4). I was thinking it might be possible to write a macro to find
the word "Code", identify that column number (knowing the rows will
always start at 6), and use that address to define where the macro
should start looking.

I would be looking for an exact match (which I think VBA automatically
does) including the same case. If a cell in the columns does not match
the master list on the 2nd sheet, I would want to turn it yellow.

I have played around with several of the "find" exapmle I have found on
this forum, but have not had any success. I have also recorded the find
command to look at the coding. I have not had much luck getting that to
work.

Any help you all offer is, as always, greatly appreciated!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=526811