We're building a spreadsheet that lists all the orders we need to ship out in
column A (by order number). Other columns provide data. There are about 500
orders per day.

Each day we add a new sheet and delete the oldest one, so we constantly have
the orders needing to be shipped out for the past 30 days.

I've been trying to find a way to take the orders on the newest sheet and
see if they match orders on previous sheets, then change the color of the
entry number on the newest sheet to alert me that it's an order holding over
from the previous day.

Theoretically, I'd only need to compare each record against those the
previous day.

I can't seem to find a conditional format that works properly and I don't
know all of the VBA-coding functions well enough to do this on my own.

Can someone help?

You could do this with formulas:
Add another column to Sheet1 (today's list) and reference Sheet2 with the
formula
Say that column A contains the identifier common to the orders.
Column M is where you decide to put the formula

In cell M1 use the formula
=Countif(Sheets2!M:M,A1)

fill this down and watch the fun...

You could also build a loop in a macro and have it do this for you.
The macro can be more generic and could work on any sheet you choose.
But the principal is the same.

hth

--
steveB

Remove "AYN" from email to respond
"AB" wrote in message
...
We're building a spreadsheet that lists all the orders we need to ship out
in
column A (by order number). Other columns provide data. There are about
500
orders per day.

Each day we add a new sheet and delete the oldest one, so we constantly
have
the orders needing to be shipped out for the past 30 days.

I've been trying to find a way to take the orders on the newest sheet and
see if they match orders on previous sheets, then change the color of the
entry number on the newest sheet to alert me that it's an order holding
over
from the previous day.

Theoretically, I'd only need to compare each record against those the
previous day.

I can't seem to find a conditional format that works properly and I don't
know all of the VBA-coding functions well enough to do this on my own.

Can someone help?

Is this formula going to check, say for example, Sheet1 A1 and compare it
against each Sheet2 column A entry?

What I need the formula/script to do is take that fist cell, Sheet1:A1, then
scan to see if it shows up anywhere in column A on another sheet(s). If it
does show up, say on Sheet3 A280, I want to know. Then, I want to do the
same thing for Sheet1:A2 and so on until it hits a blank.

The other twist is that I don't know what all of the pages will be called.

I was trying to write a script, and this is as far as I've gotten:

Dim varChecking As Variant
Dim strMessage As String
Dim ws As Worksheet

Range("A2").Activate
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
varChecking = ActiveCell.Value

For Each ws In ActiveWorkbook.Worksheets
If Range("A2").Value = varChecking Then
ActiveCell.Font.ColorIndex = 3
ActiveCell.Font.Bold = True
ActiveCell.Font.Italic = True
End If
Next

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell) = True

strMessage = "This page has been scanned for duplicates."
MsgBox (strMessage)

End Sub

I know this doesn't work, but I thought it would make active the cell it's
scanning duplicates for then compare it against the entire A column on every
sheet and change the way the text appears in the active cell if it does find
a duplicate. It doesn't (ha ha).

Any ideas?

A lot easier than you might expect:
Let's say that "MyDataSheet" is the name of your master list sheet
And the info you are checking is in column A on each sheet

Try this code (untested).
Note that even after it finds an occurance it still keeps searching all the
sheets - you can build a fix to this if needed.

Replace the msgbox with other code...

=======================================
Dim x as long, lrw as long, rw as long, ws as worksheet

' find number of used rows on master sheet
lrw = Sheets("MyDataSheet").Cells(Rows.COUNT, "A").End(xlUp).Row

' loop through all worksheets
For each ws in ActiveWorkbook.Worksheets
' ignor master worksheet
If ws.name < "MyDataSheet" then
'loop through all rows in master data sheet
For rw = 1 to lrw
' count occurances on sheet ws
x =
worksheetfunction.countif(ws.columns(1),sheets("My DataSheet").cells(rw,1))
' if found, show message...
if x 0 then
msgbox x & " iccurances of " &
sheets("MyDataSheet").cells(rw,1) & _
"found on sheet " & ws.name
end if
Next
end if
Next
================================================== ======
--
steveB

Remove "AYN" from email to respond
"AB" wrote in message
...
Is this formula going to check, say for example, Sheet1 A1 and compare it
against each Sheet2 column A entry?

What I need the formula/script to do is take that fist cell, Sheet1:A1,
then
scan to see if it shows up anywhere in column A on another sheet(s). If
it
does show up, say on Sheet3 A280, I want to know. Then, I want to do the
same thing for Sheet1:A2 and so on until it hits a blank.

The other twist is that I don't know what all of the pages will be called.

I was trying to write a script, and this is as far as I've gotten:

Dim varChecking As Variant
Dim strMessage As String
Dim ws As Worksheet

Range("A2").Activate
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
varChecking = ActiveCell.Value

For Each ws In ActiveWorkbook.Worksheets
If Range("A2").Value = varChecking Then
ActiveCell.Font.ColorIndex = 3
ActiveCell.Font.Bold = True
ActiveCell.Font.Italic = True
End If
Next

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell) = True

strMessage = "This page has been scanned for duplicates."
MsgBox (strMessage)

End Sub

I know this doesn't work, but I thought it would make active the cell it's
scanning duplicates for then compare it against the entire A column on
every
sheet and change the way the text appears in the active cell if it does
find
a duplicate. It doesn't (ha ha).

Any ideas?

Another question, carrying on with your COUNTIF idea:
How would I get the formula to look at more than just sheet2? For example,
could I get it to lookup Sheets 2-20?

Hi,

Try this:

Option Explicit

Sub FindDuplicates(ns, os)
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rnga As Range
Dim rngb As Range
Dim cell As Range
Set ws1 = Worksheets(ns)
Set ws2 = Worksheets(os)
' Set list of new orders
With ws1
Set rnga = .Range("a2:a" & .Cells(Rows.Count, "a").End(xlUp).Row)
End With
' set list of old orders
With ws2
Set rngb = .Range("a2:a" & .Cells(Rows.Count, "a").End(xlUp).Row)
End With

' check if new orders present in
old orders
For Each cell In rnga
If Application.CountIf(rngb, cell) 0 Then ' found ......
cell.Font.ColorIndex = 3
cell.Font.Bold = True
cell.Font.Italic = True
End If
Next cell
End Sub

Assumes "newsheet" is tab on your new orders sheet. Loops through other
worksheets

Sub test()
Dim oldsheet As String, newsheet as string
Dim i As Integer
newsheet="newsheet" <==== Change as required
For i = 1 To Worksheets.Count
oldsheet = Sheets(i).Name
If oldsheet < newsheet then
Call FindDuplicates(newsheet, oldsheet)
end if
Next i
End Sub


HTH

"AB" wrote:

Another question, carrying on with your COUNTIF idea:
How would I get the formula to look at more than just sheet2? For example,
could I get it to lookup Sheets 2-20?

That works perfectly and it's exactly what I was looking for. Thanks a
million.

On Wed, 6 Jul 2005 17:00:02 -0700, "AB" wrote:

We're building a spreadsheet that lists all the orders we need to ship out in
column A (by order number). Other columns provide data. There are about 500
orders per day.

Each day we add a new sheet and delete the oldest one, so we constantly have
the orders needing to be shipped out for the past 30 days.

I've been trying to find a way to take the orders on the newest sheet and
see if they match orders on previous sheets, then change the color of the
entry number on the newest sheet to alert me that it's an order holding over
from the previous day.

Theoretically, I'd only need to compare each record against those the
previous day.

I can't seem to find a conditional format that works properly and I don't
know all of the VBA-coding functions well enough to do this on my own.

Can someone help?

You cannot use conditional formatting to refer to "other" worksheets. So you
have to have the relevant information on the worksheet in question.

If your order numbers are in Sheet1!Column A; in some unused column (e.g.
Column AA) enter the formula =COUNTIF(Sheet2!A:A,A1) and copy it down as far as
needed.

This will give a number 0 only if the contents of A1 are not found in column A
on sheet 2. As you drag down the formula, the A1 reference will change.

Then select A1 (on Sheet 1), and:

Format/Conditional Formatting/Formula Is: =AA10

Format to taste.

Use the format painter to copy the formats down column A as far as necessary.

============================

In another post you mentioned comparisons on more than one sheet.

Use the formula as written above, but enter it into 29 adjacent columns, each
one referring to a different sheet. Then change the conditional formatting
formula to =SUM(AA1:BC1)0

OR

If your company policy allows, you could download Longre's free morefunc.xll
add-in found at http://xcell05.free.fr/english/ and use the COUNTIF.3D function
to use COUNTIF with a 3D reference. (The built-in COUNTIF does not allow 3D
references).


--ron