Hello All,
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.

On Aug 5, 2:09*pm, mrcreid wrote:
Hello All,
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.

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

On Aug 5, 3:20*pm, ryguy7272 wrote:
On Aug 5, 2:09*pm, mrcreid wrote:





Hello All,
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.

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.............- Hide quoted text -

- Show quoted text -

Whoops, sorry, wrong post!!

On Aug 5, 3:20*pm, ryguy7272 wrote:
On Aug 5, 3:20*pm, ryguy7272 wrote:





On Aug 5, 2:09*pm, mrcreid wrote:

Hello All,
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.

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.............- Hide quoted text -

- Show quoted text -

Whoops, sorry, wrong post!!- Hide quoted text -

- Show quoted text -

Take this for a spin and see how you like it:
http://www.exceltip.com/st/Compare_t...ce l/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...

On Aug 5, 2:41*pm, ryguy7272 wrote:
On Aug 5, 3:20*pm, ryguy7272 wrote:





On Aug 5, 3:20*pm, ryguy7272 wrote:

On Aug 5, 2:09*pm, mrcreid wrote:

Hello All,
If anyone could help me with a VBA Macro that willcomparetwo
worksheets in the same workbook andhighlightthedifference, 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
comparethe 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
highlightin 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 thencomparethe 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,highlightthe 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..

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.............- Hide quoted text -

- Show quoted text -

Whoops, sorry, wrong post!!- Hide quoted text -

- Show quoted text -

Take this for a spin and see how you like it:http://www.exceltip.com/st/Compare_t..._VBA_in_Micros...

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
IfSheets("Current").Cells(myRow, myCol).Value <Sheets("Previous").Cells(myRow, myCol).Value ThenSheets("Current").Cells(myRow, myCol).Interior.ColorIndex = 6
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
little sample for you to test...- Hide quoted text -

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

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"
wrote in message
...
On Aug 5, 2:41 pm, ryguy7272 wrote:
On Aug 5, 3:20 pm, ryguy7272 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
-----------------------------------------------------------------------------
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.