I wrote a macro to compare two columns of alphanumeric codes with a list
of alphamnumeric codes on another tab in the same workbook. Basically,
I want to identify codes that do not appear in the list. Here is my
code:

Sub MacroCodes()
Dim i As Long
Dim j As Long
Dim k As Long
Dim Lrow As Long

ActiveSheet.Unprotect Password:="password"
Range("A1").Select
Selection.SpecialCells(xlCellTypeLastCell).Select
Lrow = ActiveCell.Row
Lcol = ActiveCell.Column
For i = 6 To Lrow
For j = 3 To 3
Application.Goto Cells(i, j)
If Not IsNumeric(ActiveCell.Value) = True Then
If ActiveCell.Value = Selection.FormulaArray =
"=OR(EXACT(ActiveCell.Value,DropDown))" = False Then
ActiveCell.Interior.ColorIndex = 6
End If
End If
Next j
Next i
For i = 6 To Lrow
For k = 6 To 6
Application.Goto Cells(i, k)
If Not IsNumeric(ActiveCell.Value) = True Then
If ActiveCell.Value = Selection.FormulaArray =
"=OR(EXACT(ActiveCell.Value,DropDown))" = False Then
ActiveCell.Interior.ColorIndex = 6
End If
End If
Next k
Next i
ActiveSheet.Protect Password:="password", DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True,
AllowSorting:=True
Range("A1").Select
End Sub

Right now, this macro colors all entries in the two scanned columns
yellow, whether it is in the list or not. I don't think the macro
likes the array formula I am trying to use. "DropDown" is a named
range I manually entered into my workbook. Is there another way to say
"look at the active cell and find its EXACT match in the named range"?


Thanks for any advice you all offer!!! (also, if there is a more
efficient way to write this, please don't hesitate to let me know!!!!).


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

If Evaluate("OR(EXACT(" & ActiveCell.Value & ",DropDown))") Then

--
Regards,
Tom Ogilvy




"Celt" wrote in message
...

I wrote a macro to compare two columns of alphanumeric codes with a list
of alphamnumeric codes on another tab in the same workbook. Basically,
I want to identify codes that do not appear in the list. Here is my
code:

Sub MacroCodes()
Dim i As Long
Dim j As Long
Dim k As Long
Dim Lrow As Long

ActiveSheet.Unprotect Password:="password"
Range("A1").Select
Selection.SpecialCells(xlCellTypeLastCell).Select
Lrow = ActiveCell.Row
Lcol = ActiveCell.Column
For i = 6 To Lrow
For j = 3 To 3
Application.Goto Cells(i, j)
If Not IsNumeric(ActiveCell.Value) = True Then
If ActiveCell.Value = Selection.FormulaArray =
"=OR(EXACT(ActiveCell.Value,DropDown))" = False Then
ActiveCell.Interior.ColorIndex = 6
End If
End If
Next j
Next i
For i = 6 To Lrow
For k = 6 To 6
Application.Goto Cells(i, k)
If Not IsNumeric(ActiveCell.Value) = True Then
If ActiveCell.Value = Selection.FormulaArray =
"=OR(EXACT(ActiveCell.Value,DropDown))" = False Then
ActiveCell.Interior.ColorIndex = 6
End If
End If
Next k
Next i
ActiveSheet.Protect Password:="password", DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True,
AllowSorting:=True
Range("A1").Select
End Sub

Right now, this macro colors all entries in the two scanned columns
yellow, whether it is in the list or not. I don't think the macro
likes the array formula I am trying to use. "DropDown" is a named
range I manually entered into my workbook. Is there another way to say
"look at the active cell and find its EXACT match in the named range"?


Thanks for any advice you all offer!!! (also, if there is a more
efficient way to write this, please don't hesitate to let me know!!!!).


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

Thanks for the quick response Tom!

I dropped that snippet in and got a "Runtime Error '13': type
mismatch" error.

I am not quite sure why I am getting this (I have only written a few
macros with tons of help from the mvps on this site and I am still
getting my arms around VB).

I changed my original line to this:

If ActiveCell.Value = Evaluate("OR(EXACT(" & ActiveCell.Value &
",DropDown))") = False Then

did I mess something up?


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