Matching problem....
 

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

--
Message posted from http://www.ExcelForum.com

Matching problem....
 

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.

--
Regards,
Tom Ogilvy

"mjack003 " wrote in message
...
Hi,

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

Best Regards,
Mjack


---
Message posted from http://www.ExcelForum.com/

Matching problem....
 

Hi
in addition to Tom, the following site explains how to define and use
names in conditional formats:
http://www.cpearson.com/excel/cformatting.htm#DefName
Frank

Tom Ogilvy wrote:
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.


"mjack003 " wrote in
message
...
Hi,

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

Best Regards,
Mjack


---
Message posted from http://www.ExcelForum.com/

Matching problem....
 

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

--
Message posted from http://www.ExcelForum.com

Matching problem....
 

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?

--
Regards,
Tom Ogilvy

"mjack003 " wrote in message
...
Thanks Tom. That's on the right track but it would still take too long
to do all this manually. Is there anyway to set up a macro which would
scan column A in all selected worksheets, take that list of numbers,
compare to the master list, and highlight in red all the matching item
numbers in column A?

Best Regards,
Mjack


---
Message posted from http://www.ExcelForum.com/

Matching problem....
 

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

--
Message posted from http://www.ExcelForum.com

Matching problem....
 

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

--
Regards,
Tom Ogilvy





mjack003 wrote in message
...
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 called
"Teardown Inventory" which is the master list then the other three
hundred or so sheets after that are packing invoices all with ascending
numbers. The header of column A on the master sheet is A6 and on the
packing invoices A16, both headers being "Item". I need it to compare
from A7 on the master sheet down to A4500 and from A17 to A500 on the
grouped packing invoices. I hope this helps. Thanks again.

Regards,
Mjack


---
Message posted from http://www.ExcelForum.com/

Matching problem....
 

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

--
Message posted from http://www.ExcelForum.com