ExcelBanter - Need a macro to compare values in a workbook

If you select the cell you want to check and then run this macro the font
will turn red if the data is not found in the master list

Sub CheckData()
Dim foundCell As Range
Dim lookUp As String
lookUp = ActiveCell.Value
With Worksheets("Sheet1").Range("A1:A500")
Set foundCell = .Find(lookUp, LookIn:=xlValues)
End With
If foundCell Is Nothing Then ActiveCell.Font.ColorIndex = 3
End Sub

However there are several drawbacks to this method. For example, if you
subsequently change the data in the lookup cell to somthing that does exist
in the list the font will still be red.

A better way of doing this would be to use a formula like vlookup. Lets say
the value you want to check is in cell A2 on sheet 2. In cell B2 enter the
formula:

=IF(ISNA(VLOOKUP(A2,Sheet1!$A$1:$A$500,1,0)),"Not Found","")

You can then format the font in cell B2 to be large, bold and red or
whatever to make it more obvious.

Hope this helps
Rowan

"DVnet" wrote:

I have a workbook with 52 worksheets. The first 2 hold base data fro the
rest. I need a macro that will compare one cell to a range of data from the
first 2 worksheets.
For example:on worksheet one I have a range a1:a500,consider this the base
data. On another worksheet i have one cell of data that needs to be compared
to the range mentioned above. If the data in the cell does not match the
range, it should change the data in the cell red.

As you can tell I am new at this, any help would be appreciated
Thanks