macro

A

auditgirl

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.
 
B

Bob Phillips

Sub CompareMacro()
Dim cell As Range

With Worksheets("Sheet1")

For Each cell In .UsedRange

If cell.Value = Worksheets("Sheet2").Range(cell.Address).Value
Then

cell.Interior.Colrindex = 5
End If
Next cell
End With
End Sub
 
X

XP

Here is one way, but I'm sure this will be trumped by someone out there with
a better method:

'book1 is the book to be highlighted
'book2 is the control book
'both files must be open
'set the following constants as appropriate
'place code in a standard code module (correct for wrapping) and run it

Const msBook1 As String = "CompareBK1.xls"
Const mcsSheet1 As String = "Sheet1"

Const msBook2 As String = "CompareBK2.xls"
Const mcsSheet2 As String = "Sheet1"

Private mvaData() As Variant
Private msaCell() As String

Sub test_it()

Dim rCell As Range
Dim lX As Long
Workbooks(msBook2).Activate
Worksheets(mcsSheet2).Activate
For Each rCell In ActiveSheet.UsedRange.Cells
lX = lX + 1
ReDim Preserve mvaData(lX)
ReDim Preserve msaCell(lX)
mvaData(lX) = rCell.Value
msaCell(lX) = rCell.Address
Next rCell

Workbooks(msBook1).Activate
Worksheets(mcsSheet1).Activate
For Each rCell In ActiveSheet.UsedRange.Cells
For lX = 1 To UBound(msaCell)
If Range(msaCell(lX)).Value = mvaData(lX) Then
Range(msaCell(lX)).Interior.ColorIndex = 6
Next lX
Next rCell

End Sub

Hope this helps...
 
L

Lars-Åke Aspelin

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.
Click to expand...

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
 
O

Otto Moehrbach

A slight modification to Bob's macro to color BOTH cells. HTH Otto
Sub CompareMacro()
Dim cell As Range
With Worksheets("Sheet1")
For Each cell In .UsedRange
If cell.Value = Worksheets("Sheet2").Range(cell.Address).Value
Then
cell.Interior.Colorindex = 5
Worksheets("Sheet2").Range(cell.Address).Interior.ColorIndex
= 5
End If
Next cell
End With
End Sub
 
R

ricky

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,
 
R

ricky

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,
 
Z

Zerro

ricky said:
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,
Click to expand...
 
Z

Zerro

Why do none of these work for me.
I am using xp and Excel 2003

Sub CompareMacro()
Dim cell As Range
With Worksheets("Sheet1")
For Each cell In .UsedRange
If cell.Value = Worksheets("Sheet2").Range(cell.Address).Value
Then

The above line does not work

cell.Interior.ColorIndex = 5
Worksheets("Sheet2").Range(cell.Address).Interior.ColorIndex = 5
End If
Next cell
End With
End Sub

I have 2 workbooks open and renamed to suit above macro
 
Top