Matching problem....

  • Thread starter Thread starter mjack003
  • Start date Start date
M

mjack003

Hi,

I need to find a way to match all numbers in column A of all selecte
sheets in a workbook with a list of numbers in column A of a maste
list on the first worksheet. If the function returns True, I need i
to highlight that entire row in a different color. Any help i
appreciated because I'm stumped.

Best Regards,
Mjac
 
use conditional formatting and countif. If conditional formatting says you
can not refer to another sheet, then create a defined name that refers to
your master list (insert=>Name=>define) and use that as the argument to your
countif.
 
Thanks Tom. That's on the right track but it would still take too lon
to do all this manually. Is there anyway to set up a macro which woul
scan column A in all selected worksheets, take that list of numbers
compare to the master list, and highlight in red all the matching ite
numbers in column A?

Best Regards,
Mjac
 
Is column B blank on Each sheet? If not, how far over does the data go and
will the first row of the last column always have a value in it? Is the
first row headers (rather than data that needs to be checked - assumes data
table starts in A1 with first row headers - correct?).
What is the name of the master sheet.?

are the master sheet and the other sheets in the same workbook?
 
Hi Tom,

Since my other post was deleted I hope you get this.
I need it to highlight from column A to column F on the master sheet.
All sheets are contained in the same workbook. The first calle
"Teardown Inventory" which is the master list then the other thre
hundred or so sheets after that are packing invoices all with ascendin
numbers. The header of column A on the master sheet is A6 and on th
packing invoices A16, both headers being "Item". I need it to compar
from A7 on the master sheet down to A4500 and from A17 to A500 on th
grouped packing invoices. I hope this helps. Thanks again.

Regards,
Mjac
 
Sub CheckMatches()
Dim masv()
Dim rw As Long, v, i As Long, rng As Range
Dim sh As Worksheet, cell As Range
ReDim masv(1 To (Worksheets.Count - 1) * 484)
rw = 1
For Each sh In Worksheets
If LCase(sh.Name) <> "teardown inventory" Then
Set rng = sh.Range("A17:A500")
v = rng.Value
For i = LBound(v, 1) To UBound(v, 1)
If Len(Trim(v(i, 1))) <> 0 Then
masv(rw) = LCase(v(i, 1))
rw = rw + 1
End If
Next
End If
Next
ReDim Preserve masv(1 To rw - 1)
Set sh = Worksheets("Teardown Inventory")
For Each cell In sh.Range("A6:A4500")
For i = LBound(masv) To UBound(masv)
If LCase(cell.Value) = masv(i) Then
cell.Resize(1, 6).ColorIndex = 6
Exit For
End If
Next
Next

End Sub
 
Hi Tom,

I tried running the macro there is an error on the following line.
ran a few tests and everything else seems to be fine, Just this on
line:

"cell.Resize(1, 6).ColorIndex = 6"

Thank you for your help.

Best Regards,
Mjac
 
Back
Top