Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
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 -- __________________________________ HTH Bob "auditgirl" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
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... "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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
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 "Bob Phillips" wrote in message ... 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 -- __________________________________ HTH Bob "auditgirl" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
"ricky" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |