Compare two worksheets in the same workbook and highlight difference
mrcreid
If anyone could help me with a VBA Macro that will compare two
worksheets in the same workbook and highlight the difference, it would
be greatly appreciated.
Both worksheets have the same number of columns (12) but not the same
number of rows. The first worksheet is called "CURRENT" that has the
current inventory of all our computer assets for the month. The
second worksheet is called "PREVIOUS" that has last month inventory in
it.
Using the serial number column (F2) as the key from current worksheet,
search previous worksheet to see if it exist. If it exist, then
compare the row of data in current worksheet against previous
worksheet where the serial number matches. If they do not match
hightight the entire row in the current worksheet. Also if possible
highlight in read the column cell data that has the mismatch.
The first part i was able to put together, where i uses the serial
number column in current sheet and look to see if it exist in the
previous sheet. If it exist then compare the row, if the rows do not
match, then hightlight the entire row in the current worksheet. What
i am unable to do is the second part, highlight the cell data in the
column where the data in the row does not match.
Listed below is the code thus far.
Sub SearchHighlight()
'--------------------------------------
Sheets("Current").Select
Dim rng As Range
Dim lRow As Long, lRowT As Long
Dim iCol As Integer
Dim bln As Boolean
Set rng = Worksheets("Previous").Range("F2").CurrentRegion
For lRow = 1 To Range("F2").CurrentRegion.Rows.Count
bln = True
For lRowT = 1 To rng.Rows.Count
For iCol = 1 To 12
If Cells(lRow, iCol) <> rng(lRowT, iCol) Then
bln = False
Exit For
End If
Next iCol
If bln = True Then
Exit For
ElseIf lRowT < rng.Rows.Count Then
bln = True
End If
Next lRowT
If bln = False Then
Range(Cells(lRow, 1), Cells(lRow, 12)).Interior.ColorIndex =
6
End If
Next lRow
End Sub
Any and all help with regards to this matter is greatly appreciated.
ryguy7272
I tried 2 ideas below:
Dim itm As Variant
If itm.innterText Like "*SubmitForm_Name*" Then
Dim objCell As Object
If objCell.innerText Like "*SubmitForm_Name*" Then
...sill the same thing.............
ryguy7272
>
> - Show quoted text -
Whoops, sorry, wrong post!!
ryguy7272
>
> - Show quoted text -
Take this for a spin and see how you like it:
http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html
Or, perhaps something liek this:
Sub SheetCompare()
' Change the 25 to the last row of your data here
For myRow = 1 To 200
For myCol = 1 To 12
If Sheets("Current").Cells(myRow, myCol).Value <>
Sheets("Previous").Cells(myRow, myCol).Value Then
Sheets("Current").Cells(myRow, myCol).Interior.ColorIndex = 6
Else
Sheets("Current").Cells(myRow, myCol).Interior.ColorIndex = xlNone
End If
Next myCol
Next myRow
End Sub
Of course the # of used rows can be determined dynamically; just a
little sample for you to test...
mrcreid
> On Aug 5, 3:20 pm, ryguy7272 <ryanshu...@gmail.com> wrote:
>
>
>
>
>
> > On Aug 5, 3:20 pm, ryguy7272 <ryanshu...@gmail.com> wrote:
>
> > > On Aug 5, 2:09 pm, mrcreid <mrcrei...@gmail.com> wrote:
>
> > > > Hello All,
> > > > If anyone could help me with a VBA Macro that willcomparetwo
> > > > be greatly appreciated.
>
> > > > Both worksheets have the same number of columns (12) but not the same
> > > > number of rows. The first worksheet is called "CURRENT" that has the
> > > > current inventory of all our computer assets for the month. The
> > > > second worksheet is called "PREVIOUS" that has last month inventory in
> > > > it.
>
> > > > Using the serial number column (F2) as the key from current worksheet,
> > > > search previous worksheet to see if it exist. If it exist, then
> > > > worksheet where the serial number matches. If they do not match
> > > > hightight the entire row in the current worksheet. Also if possible
>
> > > > The first part i was able to put together, where i uses the serial
> > > > number column in current sheet and look to see if it exist in the
> > > > match, then hightlight the entire row in the current worksheet. What
>
> Or, perhaps something liek this:
> Sub SheetCompare()
> ' Change the 25 to the last row of your data here
> For myRow = 1 To 200
> For myCol = 1 To 12
> ElseSheets("Current").Cells(myRow, myCol).Interior.ColorIndex = xlNone
> End If
> Next myCol
> Next myRow
> End Sub
>
> Of course the # of used rows can be determined dynamically; just a
>
> - Show quoted text -
This appears to work however, not exactly. with the following sample
data
Current
Lname Fname PCName SerialNum Make Model
-----------------------------------------------------------------------------
Mouse Mickey PC-001 A123B456 HP 7900
Duck Donald PC-002 C789D013 HP 7100
Woman Wonder PC-003 E456F789 HP 7900
Mouse Mighty PC-004 G012H345 HP 7900
Duck Howard PC-005 I678J9012 HP 7900
Doo Scooby PC-006 K345L678 HP 7900
Dog Shaggy PC-007 M901N234 HP 7900
Previous
Lname Fname PCName SerialNum Make Model
-----------------------------------------------------------------------------
Mouse Jerry PC-001 A123B456 HP 7900
Duck Donald PC-002 C789D013 HP 7100
Woman Wonder PC-003 E456F789 HP 7900
Mouse Mighty PC-004 G012H345 HP 7900
Hawk Junior PC-005 I678J9012 HP 7900
So using the serial number in current sheet, find that serial number
in sheet1. When it finds that serial number, compare the entire row
from Current sheet to Previous sheet. If the entire row matches, do
nothing, and if anything in the rows that do not match, highlight it
in the current sheet...
So in Current sheet the following should be hightlighted
Lname Fname PCName SerialNum Make Model
-----------------------------------------------------------------------------
Mouse Mickey PC-001 A123B456 HP 7900 <- Fname column
<<Mickey>> should be highlighted
Duck Donald PC-002 C789D013 HP 7100
Woman Wonder PC-003 E456F789 HP 7900
Mouse Mighty PC-004 G012H345 HP 7900
Duck Howard PC-005 I678J9012 HP 7900<<- Lname and
Fname should be hightlighted - name changed
Doo Scooby PC-006 K345L678 HP 7900<- Entire row
Should be hightlighted because not found in Previous sheet
Dog Shaggy PC-007 M901N234 HP 7900<- Entire row
Should be hightlighted because not found in Previous sheet
The code that you provided is very simple, easy, and very short to
what i have. I really like it. the only problem is that in order to
work properly, both sheets (Current and Previous) have to be the same,
if not it will highlight all rows. in the sample above, if i added
another name in the current sheet above Micky Mouse, then all rows
below would be highlighted because it does not match the Previous
sheet. (I hope i explained that right)
Is there a way to modify your code to do this...your assistance is
greatly appreciated.
Jim Cone
individual row and compare the concatenated values to each other.
That requires a loop thru each row in both worksheets.
--
Jim Cone
Portland, Oregon USA
Compare | match | uniques: http://tinyurl.com/XLCompanion
"mrcreid" <mrcr...@gmail.com>
wrote in message
news:dfce2c9b-9c87-46fb...@t26g2000yqt.googlegroups.com...
ryguy7272
> To compare rows, you will have to concatenate the cell values in each
> individual row and compare the concatenated values to each other.
> That requires a loop thru each row in both worksheets.
> --
> Jim Cone
> Portland, Oregon USA
> Compare | match | uniques:http://tinyurl.com/XLCompanion
>
> wrote in messagenews:dfce2c9b-9c87-46fb...@t26g2000yqt.googlegroups.com...
I think this will do what you want:
Sub Compare()
With Sheets("Current")
Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = .Range("A1:F" & Sh1LastRow)
End With
With Sheets("Previous")
Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh2Range = .Range("A1:F" & Sh2LastRow)
End With
'compare sheet 1 with sheet 2
For Each Sh1cell In Sh1Range
Set c = Sh2Range.Find(what:=Sh1cell, LookIn:=xlValues)
If c Is Nothing Then
Sh1cell.Interior.ColorIndex = 3
Sh1cell.EntireRow.Interior.ColorIndex = 3
End If
Next Sh1cell
End Sub
Post back and let everyone know if it meets your business requirement.
mrcreid
> On Aug 6, 10:50 am, "Jim Cone" <james.cone...@comcast.netXXX> wrote:
>
> > To compare rows, you will have to concatenate the cell values in each
> > individual row and compare the concatenated values to each other.
> > That requires a loop thru each row in both worksheets.
> > --
> > Jim Cone
> > Portland, Oregon USA
> > Compare | match | uniques:http://tinyurl.com/XLCompanion
>
> > "mrcreid" <mrcrei...@gmail.com>
> > wrote in messagenews:dfce2c9b-9c87-46fb...@t26g2000yqt.googlegroups.com...
> > On Aug 5, 2:41 pm, ryguy7272 <ryanshu...@gmail.com> wrote:> On Aug 5, 3:20 pm, ryguy7272 <ryanshu...@gmail.com> wrote:
>
> > This appears to work however, not exactly. with the following sample data
>
> > Current
> > Lname Fname PCName SerialNum Make Model
> > Mouse Mickey PC-001 A123B456 HP 7900
> > Duck Donald PC-002 C789D013 HP 7100
> > Woman Wonder PC-003 E456F789 HP 7900
> > Mouse Mighty PC-004 G012H345 HP 7900
> > Duck Howard PC-005 I678J9012 HP 7900
> > Doo Scooby PC-006 K345L678 HP 7900
> > Dog Shaggy PC-007 M901N234 HP 7900
>
> > Previous
> > Lname Fname PCName SerialNum Make Model
> > Mouse Jerry PC-001 A123B456 HP 7900
> > Duck Donald PC-002 C789D013 HP 7100
> > Woman Wonder PC-003 E456F789 HP 7900
> > Mouse Mighty PC-004 G012H345 HP 7900
> > Hawk Junior PC-005 I678J9012 HP 7900
>
> > So using the serial number in current sheet, find that serial number
> > in sheet1. When it finds that serial number, compare the entire row
> > from Current sheet to Previous sheet. If the entire row matches, do
> > nothing, and if anything in the rows that do not match, highlight it
> > in the current sheet...
> > So in Current sheet the following should be hightlighted
>
> > Lname Fname PCName SerialNum Make Model
This solution sorta works but not exactly. I need to see if the
Serial Number in sheet2 exist in sheet1, when it finds the serial
number is sheet1, then do the comparison of the row and highlight if
not matching.
My ultimate goal is the following:
-----------------------------------------------
01. Look in Column "D" labeled SerialNum in Sheet2
02. Grab that SerialNum and search in sheet1 to see if it exist
03. Sheet1 may have more rows than sheet2 to or vice versa but the
same number of columns
04. Once it finds the SerialNum in Sheet1, compare Sheet2 row with
Sheet1 row where matching SerialNum, if not matching highlight
mismatch column in that row
05. If SerialNum in sheet2 does not exist in Sheet1, hightlight
SerialNum in sheet2 in different color (Green)
06. Extra -> where mismatching, add a comment tag in sheet2 that
illustrates the mismatching data.
The problem that i am having with the provided code, is that it does
not do a search of the SerialNum and compares that row. I copied and
pasted the provided code and that's what i keep getting.
I will try again and let you know what i get....