C
Celt
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!!!!).
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!!!!).