Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a macro to compare values in a workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy values to Workbook | Excel Discussion (Misc queries) | |||
Excel VBA Macro to compare 2 worksheets for same values | Excel Worksheet Functions | |||
Passing selected workbook name and values to a macro | Excel Worksheet Functions | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) | |||
Macro to compare values and return exact matching value | Excel Programming |