Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Cells
Hello: Iam trying to compare several columns on two sheets. For example compare all the contents of column A,B,C,D on sheet1 with the corresponding columns A,B,C,D on sheet2. Once it runs the comparison, all cells which do not contain the same corresponding values (ex sheet1.cell(a1) and sheet2.cell(a1)) will be colored yellow. A Message box would also appear telling how many incorrect matches have been found. Please can someone help me Thank you Also could such a macro be used on more than two sheets simultaneously. -- kwedde01 ------------------------------------------------------------------------ kwedde01's Profile: http://www.excelforum.com/member.php...o&userid=24156 View this thread: http://www.excelforum.com/showthread...hreadid=386563 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Cells
Hi here ya go
Sub CompareSheets() Dim Cell As Range Sheet1.Range("A:D").Interior.ColorIndex = xlColorIndexNone Sheet2.Range("A:D").Interior.ColorIndex = xlColorIndexNone For Each Cell In Sheet1.Range("A:D").Cells If Cell.Value < Sheet2.Range(Cell.Address).Value Then Cell.Interior.ColorIndex = 6 Sheet2.Range(Cell.Address).Interior.ColorIndex = 6 End If Next Cell End Sub Best wishes, Bill Benson, CPA/MBA President XLCREATIONS.COM http://www.xlcreations.com With you in the fight against "Automation Apathy" © "kwedde01" wrote in message ... Hello: Iam trying to compare several columns on two sheets. For example compare all the contents of column A,B,C,D on sheet1 with the corresponding columns A,B,C,D on sheet2. Once it runs the comparison, all cells which do not contain the same corresponding values (ex sheet1.cell(a1) and sheet2.cell(a1)) will be colored yellow. A Message box would also appear telling how many incorrect matches have been found. Please can someone help me Thank you Also could such a macro be used on more than two sheets simultaneously. -- kwedde01 ------------------------------------------------------------------------ kwedde01's Profile: http://www.excelforum.com/member.php...o&userid=24156 View this thread: http://www.excelforum.com/showthread...hreadid=386563 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Cells
With tally and message box...
Sub CompareSheets() Dim Cell As Range, Dim i As Long Sheet1.Range("A:D").Interior.ColorIndex = xlColorIndexNone Sheet2.Range("A:D").Interior.ColorIndex = xlColorIndexNone For Each Cell In Sheet1.Range("A:D").Cells If Cell.Value < Sheet2.Range(Cell.Address).Value Then Cell.Interior.ColorIndex = 6 i = i + 1 Sheet2.Range(Cell.Address).Interior.ColorIndex = 6 End If Next Cell If i 0 Then MsgBox "There " & IIf(i 1, "were ", "was ") & _ Format(i, "#,##0") & " mismatched " & IIf(i 1, "values.", "value.") End Sub "kwedde01" wrote in message ... Hello: Iam trying to compare several columns on two sheets. For example compare all the contents of column A,B,C,D on sheet1 with the corresponding columns A,B,C,D on sheet2. Once it runs the comparison, all cells which do not contain the same corresponding values (ex sheet1.cell(a1) and sheet2.cell(a1)) will be colored yellow. A Message box would also appear telling how many incorrect matches have been found. Please can someone help me Thank you Also could such a macro be used on more than two sheets simultaneously. -- kwedde01 ------------------------------------------------------------------------ kwedde01's Profile: http://www.excelforum.com/member.php...o&userid=24156 View this thread: http://www.excelforum.com/showthread...hreadid=386563 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Cells
Hey thanks alot for the help, by any chance if i wanted it to search every cell on the sheet1 and every cell on sheet2, without having to specify a certain range, how would I do that? -- kwedde01 ------------------------------------------------------------------------ kwedde01's Profile: http://www.excelforum.com/member.php...o&userid=24156 View this thread: http://www.excelforum.com/showthread...hreadid=386563 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Cells
For Each Cell In Sheet1.Range("A:D").Cells
change to this For Each Cell In Sheet1.Range("A:IV").Cells But you better be ready to take a long break - that's a lot of cells to check I would consider For Each Cell In Sheet1.UsedRange or build code to find the Last Cell and restrict your range to A1:LastCell dim lr as long, lc as long lr = Sheet1.Cells.SpecialCells(xlLastCell).Row lc= Sheet1.Cells.SpecialCells(xlLastCell).Column For each cell in range(cells(1,1),cells(lr,lc)) -- steveB Remove "AYN" from email to respond "kwedde01" wrote in message ... Hey thanks alot for the help, by any chance if i wanted it to search every cell on the sheet1 and every cell on sheet2, without having to specify a certain range, how would I do that? -- kwedde01 ------------------------------------------------------------------------ kwedde01's Profile: http://www.excelforum.com/member.php...o&userid=24156 View this thread: http://www.excelforum.com/showthread...hreadid=386563 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare 2 column cells and return the adjacent columns cells data of the cell | Excel Worksheet Functions | |||
Compare 1 cell to column of cells returning adjacent cells info? | Excel Worksheet Functions | |||
How do I compare cells and if FALSE compare to next cell in EXCEL | Excel Worksheet Functions | |||
Compare two cells from reference cells | Excel Worksheet Functions | |||
Compare 2 cells in 2 worksheets, rewrite one of the cells | Excel Programming |