also

Column A = check #
column B = date
column C= amount

I may need to tell the macro if
check (c)
or
deposit (d)

if (c) match on column A
if (d) match on column B and C

thanks,

"ricky" wrote:

i am trying to do a bank rec where i match on:
check number (exact match)
deposit amount and date (both field need to match)

i need to highlight the cells that match in both sheets.
thanks,


"Lars-Åke Aspelin" wrote:

On Mon, 4 Aug 2008 12:52:10 -0700, auditgirl
wrote:

I do audits with 2 worksheets up at a time. I need a macro/formula that will
compare the numbers in each worksheet and if one number matches another
number in the other worksheet, then those two cells with the matching numbers
are colored in yellow. In the end whatever's not yellowed in are my "problem
numbers." I've been searching for 2 days now and haven't found anything.

As you dont state anything about the position of the numbers within
the respective sheet I just assume that they can be anywhere within a
given range.

The following macro colors the matching cells. If there are several
cells in the second range with the same number as a cell in the first
range, only the first one is considered a match.

Sub audit(r1 As Range, r2 As Range)

' first clear any previous coloring in the two ranges
For Each c1 In r1
c1.Interior.ColorIndex = 0
Next c1
For Each c2 In r2
c2.Interior.ColorIndex = 0
Next c2

'match the numbers in r1 to the numbers in r2
For Each c1 In r1
For Each c2 In r2
If (c1.Value = c2.Value) And (c2.Interior.ColorIndex < 6) Then
c1.Interior.ColorIndex = 6
c2.Interior.ColorIndex = 6
End If
Next c2
Next c1
End Sub


Call this macro from another macro like this

Sub doaudit()
audit Worksheets("Sheet1"):Range("A1:K100"),
Worksheets("Sheet2").Range("A1:K100")
End Sub

Hope this helps / Lars-Åke