Compare and highlight differences in 2 worksheets in same workbookand list differences in 3rd worksh

[Joshua Houck]

Hello everyone,
I am trying to create a macro to highlight the differences in 2
worksheets within the same workbook. I would like to highlight the
differences and show them in Sheet1. I would also like to display the
changes in Sheet3(Activity changes) Sheet1 name is "This Weeks POR"
and Sheet2 name is "Last Weeks POR" Sheet3 name is "Activity Changes".
Here is a link to the file in question -
https://docs.google.com/uc?id=0B8VP...ZTUtZWVlNDVkYWMyZDI2&export=download&hl=en_US
Thanks in advance for your help. Any response is appreciated and if
you can show as much how you came up with logical solution would help.
I am a beginner and am taking the learn as I go approach.
Josh

 

[Joshua Houck]

Hello everyone,
I am trying to create a macro to highlight the differences in 2
worksheets within the same workbook. I would like to highlight the
differences and show them in Sheet1. I would also like to display the
changes in Sheet3(Activity changes) Sheet1 name is "This Weeks POR"
and Sheet2 name is "Last Weeks POR" Sheet3 name is "Activity Changes".
Here is a link to the file in question -https://docs.google.com/uc?id=0B8VP5dFKXt9hODA2OWNmM2ItMDQ5OC00MTAyLW...
Thanks in advance for your help. Any response is appreciated and if
you can show as much how you came up with logical solution would help.
I am a beginner and am taking the learn as I go approach.
Josh

It looks like this code was able to highlight the information i needed
in Sheet1, but now I need to put that highlighted data in sheet 3.
Sub comparesheets()
For Each cl In Sheets("sheet2").UsedRange
If cl.Value <> Sheets("Sheet1").Cells(cl.Row, cl.Column) Then
cl.Interior.Color = RGB(0, 0, 255)
End If
Next cl
End Sub

 

[Joshua Houck]

I think I have figured out how to highlight the differences in This
Weeks POR and Last Weeks POR using this code-
Sub comparesheets()
For Each cl In Sheets("This Weeks POR").UsedRange
If cl.Value <> Sheets("Last Weeks POR").Cells(cl.Row,
cl.Column) Then
cl.Interior.Color = RGB(0, 0, 255)
End If
Next cl
End Sub
Now I just have to figure out how to take the highlighted data and
format them sheet 3(activity changes)

 

[Don Guillett]

I think I have figured out how to highlight the differences in This
Weeks POR and Last Weeks POR using this code-
Sub comparesheets()
    For Each cl In Sheets("This Weeks POR").UsedRange
        If cl.Value <> Sheets("Last Weeks POR").Cells(cl.Row,
cl.Column) Then
            cl.Interior.Color = RGB(0, 0, 255)
        End If
    Next cl
End Sub
Now I just have to figure out how to take the highlighted data and
format them sheet 3(activity changes)

You didn't mention what you want to copy & your link didn't work. Try
If cl.Value <> Sheets("Last Weeks POR").Cells(cl.Row, cl.Column)
Then
cl.Interior.Color = RGB(0, 0, 255)
cl.entirerow.copy sheets("sheet 3").cells(rows.count,
1).end(xlup).offset(1)
end if

 

[Joshua Houck]

You didn't mention what you want to copy & your link didn't work. Try
  If cl.Value <> Sheets("Last Weeks POR").Cells(cl.Row, cl.Column)
Then
             cl.Interior.Color = RGB(0, 0, 255)
cl.entirerow.copy sheets("sheet 3").cells(rows.count,
1).end(xlup).offset(1)
end if

Thanks for the response, I have fixed the link and I posted what I
have so far. This should give you a better idea what I'm looking to
do.
https://docs.google.com/leaf?id=0B8...NThmMS00NDRjLTliODItMTFlMTNjNDg5ZmU1&hl=en_US

 

[Jim Cone]

Download from... http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)



"Joshua Houck" <[email protected]>
wrote in message

Thanks for the response, I have fixed the link and I posted what I
have so far. This should give you a better idea what I'm looking to
do.
https://docs.google.com/leaf?id=0B8...NThmMS00NDRjLTliODItMTFlMTNjNDg5ZmU1&hl=en_US

 

[Joshua Houck]

Download from...http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.
--
Jim Cone
Portland, Oregon USA .http://www.mediafire.com/PrimitiveSoftware.
(free and commercial excel programs)

"Joshua Houck" <[email protected]>
wrote in message
Thanks for the response, I have fixed the link and I posted what I
have so far. This should give you a better idea what I'm looking to
do.https://docs.google.com/leaf?id=0B8VP5dFKXt9hZTU5NjVlNDItNThmMS00NDRj...

Thanks Jim,
I have been going over the logic to try and take the highlighted cell
differences and place them in the Activity Changes worksheet "new
value" column. But to take it one step further also move the column
headers of EventID, Entity Code, Life, CEID, and Activity associated
with each cells differences. I am not sure if I could write it in the
macro I already have, create a new macro, or use an add-in like you
used to compare to new worksheet, but with customized headers to
match the format I am trying to achieve. Like I stated earlier, I am a
newby, but trying to figure out as I go. Thanks for your interest.
Josh

 

[Jim Cone]

Maybe...
Copy the two sheets.
Clear the contents below the column headers.
When cells don't agree, enter those values into the copied sheets.
You end up with the sheets just showing the problem cells/values.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)





"Joshua Houck" <[email protected]>
wrote in message

Download from...http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware.
(free and commercial excel programs)



"Joshua Houck" <[email protected]>
wrote in message
Thanks for the response, I have fixed the link and I posted what I
have so far. This should give you a better idea what I'm looking to
do.https://docs.google.com/leaf?id=0B8VP5dFKXt9hZTU5NjVlNDItNThmMS00NDRj...

Thanks Jim,
I have been going over the logic to try and take the highlighted cell
differences and place them in the Activity Changes worksheet "new
value" column. But to take it one step further also move the column
headers of EventID, Entity Code, Life, CEID, and Activity associated
with each cells differences. I am not sure if I could write it in the
macro I already have, create a new macro, or use an add-in like you
used to compare to new worksheet, but with customized headers to
match the format I am trying to achieve. Like I stated earlier, I am a
newby, but trying to figure out as I go. Thanks for your interest.
Josh

 

[Walter Briscoe]

In message <[email protected]> of Sat, 13 Aug 2011 19:18:33 in
microsoft.public.excel.programming, Jim Cone

Download from... http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.

Jim,
Please blow your trumpet. I see you offer a 3 week trial.
What does the software do? I downloaded XLCompanion.zip and had a look.
What does a license cost? XL Companion Read Me.doc seems to be written
in a clever fashion so Ctrl-F does not work; nor does text selection.

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

2) Deep comparison. I get workbooks from a company, but have no access
to any technical people.
The latest workbooks are flawed. (Text does not fit in a textbox.)
I want to compare a good textbox and a bad textbox at the VBA level.
If I can analyse the flaw, there is a small chance it will be fixed.
I downloaded XLCompanion.zip, read it only compares cells and infer it
does not fill my need. ;(

I continue to use Excel 2003.

I would value suggestions of products likely to support those needs!

P.S. I wrote a simple shape-dump routine (showing Left, Top, Height,
Width, TextFrame.Characters.Font(FontStyle, Name, Size) and
TextFrame.Characters), but it showed nothing.
TextFrame.Characters.Text limits itself to 255 characters.
This code seems to grab it all - only tested to 321!
With V.TextFrame
For I = 1 To .Characters.Count Step 255
S = S & .Characters(Start:=I).Text ' Text limit is 255
Next I
End With
It took me a little while to deduce that code after googling.
The hard thing was placing that "Start:=I".
Somebody may find the snippet useful.

 

[Jim Cone]

Walter,

The XL Companion Read Me file (Word 2002) is protected, but requires no password to unprotect.
The protection is to prevent accidental changes to or repositioning of pictures.
The XL Companion program can do the following:

Compare ...
worksheets, cell by cell (any of 9 differences)
selections, cell by cell
each row to all rows
lists (will color any matches)

Count...
unique cells (or color them)
unique rows
unique words

Remove, color or clear...
Identical rows from a worksheet
(a row is defined by the columns the user selects)

Delete, color or clear...
rows that meet criteria the user specifies

Clean Data
3 intensity levels

Find
multiple items on multiple sheets

The program sells for $39.00
Download from: http://www.mediafire.com/PrimitiveSoftware
--
Jim Cone
Portland, Oregon USA
http://excelusergroup.org/media/
(Formats & Styles xl add-in: lists/removes unused styles & number formats)





"Walter Briscoe" <[email protected]>
wrote in message

 

[GS]

Walter Briscoe pretended :

In message <[email protected]> of Sat, 13 Aug 2011 19:18:33 in
microsoft.public.excel.programming, Jim Cone


Jim,
Please blow your trumpet. I see you offer a 3 week trial.
What does the software do? I downloaded XLCompanion.zip and had a look.
What does a license cost? XL Companion Read Me.doc seems to be written
in a clever fashion so Ctrl-F does not work; nor does text selection.

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

Have you looked at using VB's Put/Get functions?

 

[Don Guillett]

Walter,

The XL Companion Read Me file (Word 2002) is protected, but requires no password to unprotect.
The protection is to prevent accidental changes to or repositioning of pictures.
The XL Companion program can do the following:

Compare ...
  worksheets, cell by cell (any of 9 differences)
  selections, cell by cell
  each row to all rows
  lists (will color any matches)

Count...
  unique cells (or color them)
  unique rows
  unique words

Remove, color or clear...
  Identical rows from a worksheet
  (a row is defined by the columns the user selects)

Delete, color or clear...
  rows that meet criteria the user specifies

Clean Data
  3 intensity levels

Find
  multiple items on multiple sheets

The program sells for $39.00
Download from:  http://www.mediafire.com/PrimitiveSoftware
--
Jim Cone
Portland, Oregon USAhttp://excelusergroup.org/media/
(Formats & Styles xl add-in:  lists/removes unused styles & number formats)

"Walter Briscoe" <[email protected]>
wrote in message










- Show quoted text -

On the COMPARE question. Since you have an xlsm file you can copy both
sheets to a single sheet and simply use xl2007 or xl2010
data>duplicates..... I don't understand the red,blue,green logic.

 

[Walter Briscoe]

In message <[email protected]> of Sun, 14 Aug 2011 07:46:43 in
microsoft.public.excel.programming, Jim Cone

Walter,

The XL Companion Read Me file (Word 2002) is protected, but requires no
password to unprotect.

Thank you. I have no experience with protected files.

The protection is to prevent accidental changes to or repositioning of
pictures.
The XL Companion program can do the following:

Compare ...
worksheets, cell by cell (any of 9 differences)

[snip]

Thank you for that useful list.
I might pay that 39USD if it compared shapes.

 

[Walter Briscoe]

Walter Briscoe pretended :
[snip]

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables.
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.

Have you looked at using VB's Put/Get functions?

No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.
I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.

e.g. I want to see something like
ActiveCell Range
AddIndent False Variant/Boolean
AllowEdit False Boolean
....

I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.

[snip]

 

[GS]

Walter Briscoe presented the following explanation :

Walter Briscoe pretended :
[snip]

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables.
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.

Have you looked at using VB's Put/Get functions?

No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.

Get/Put are standard VB[A] file I/O functions. My understanding of your
post is that you want to write these variables and their respective
values to a text file. That's what Get/Put would be used for to
Read/Write the file, respectively.

Most of VBA's inherent language functionality IS pure VB!

I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.

e.g. I want to see something like
ActiveCell Range
AddIndent False Variant/Boolean
AllowEdit False Boolean
...

I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.

I believe there are utility addins out there that will let you do this
within the VBE. You'd be better off to google this for VB as I suspect

[snip]

 

[Don Guillett]

Walter Briscoe presented the following explanation :

Walter Briscoe pretended :

[snip]

I have two known unsatisfied needs for Excel:
1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;
Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables..
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.

No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.

Get/Put are standard VB[A] file I/O functions. My understanding of your
post is that you want to write these variables and their respective
values to a text file. That's what Get/Put would be used for to
Read/Write the file, respectively.

Most of VBA's inherent language functionality IS pure VB!

I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.

e.g. I want to see something like
ActiveCell                              Range
    AddIndent   False                   Variant/Boolean
    AllowEdit   False                   Boolean
...

I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.

I believe there are utility addins out there that will let you do this
within the VBE. You'd be better off to google this for VB as I suspect
you'd get more results.<g>

[snip]

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Have you tried the suggestion to combine and remove dups?

 

[GS]

Don Guillett wrote :

Walter Briscoe presented the following explanation :

In message <[email protected]> of Sun, 14 Aug 2011 11:56:58 in
Walter Briscoe pretended :

[snip]

I have two known unsatisfied needs for Excel:
1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;
Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables.
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.

Have you looked at using VB's Put/Get functions?
No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.

Get/Put are standard VB[A] file I/O functions. My understanding of your
post is that you want to write these variables and their respective
values to a text file. That's what Get/Put would be used for to
Read/Write the file, respectively.

Most of VBA's inherent language functionality IS pure VB!

I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.
e.g. I want to see something like
ActiveCell                              Range
    AddIndent   False                   Variant/Boolean
    AllowEdit   False                   Boolean
...

I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.

I believe there are utility addins out there that will let you do this
within the VBE. You'd be better off to google this for VB as I suspect
you'd get more results.<g>

[snip]

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Have you tried the suggestion to combine and remove dups?

No, not in the context that you suggested. Albeit viable as is, I'd
usually load the data into dynamic arrays and do it that way (in
earlier versions), but I'll look at your suggestion for xl2>=! Thanks
for mentioning it...

 

[Don Guillett]

Don Guillett wrote :

Walter Briscoe presented the following explanation :
In message <[email protected]> of Sun, 14 Aug 2011 11:56:58 in
Walter Briscoe pretended :
[snip]
I have two known unsatisfied needs for Excel:
1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;
Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)
No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables.
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.
Have you looked at using VB's Put/Get functions?
No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.
Get/Put are standard VB[A] file I/O functions. My understanding of your
post is that you want to write these variables and their respective
values to a text file. That's what Get/Put would be used for to
Read/Write the file, respectively.
Most of VBA's inherent language functionality IS pure VB!
I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.
e.g. I want to see something like
ActiveCell                              Range
    AddIndent   False                   Variant/Boolean
    AllowEdit   False                   Boolean
...
I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.
I believe there are utility addins out there that will let you do this
within the VBE. You'd be better off to google this for VB as I suspect
you'd get more results.<g>
[snip]
--
Garry
Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text-
- Show quoted text -

Have you tried the suggestion to combine and remove dups?

No, not in the context that you suggested. Albeit viable as is, I'd
usually load the data into dynamic arrays and do it that way (in
earlier versions), but I'll look at your suggestion for xl2>=! Thanks
for mentioning it...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Send me your file and I'll show you.

 

[GS]

Don,
I am NOT the OP! I do not have a file to send you.

That said, I'm a firm advocate of using built-in (available)
functionality over using VB[A] to do the same task because it's always
more efficient to do so. I just happen to have wrapper procedures for
doing this via arrays, and so I persist this regardless of what version
my projects run in.

I have no problem, though, to make projects version-aware so as to take
advantage of the newer version built-in features.

 

[Vacuum Sealed]

Hey there Walter

I think CCI stands for:

Cyclomatic Complexity Indicator - McCabe Cyclomatic Complexity (aka CCI or
CCN).

http://en.wikipedia.org/wiki/Cyclomatic_complexity

Cyclomatic complexity (or conditional complexity) is a software metric
(measurement). It was developed by Thomas J. McCabe, Sr. in 1976 and is used
to indicate the complexity of a program. It directly measures the number of
linearly independent paths through a program's source code. The concept,
although not the method, is somewhat similar to that of general text
complexity measured by the Flesch-Kincaid Readability Test.

Cyclomatic complexity is computed using the control flow graph of the
program: the nodes of the graph correspond to indivisible groups of commands
of a program, and a directed edge connects two nodes if the second command
might be executed immediately after the first command. Cyclomatic complexity
may also be applied to individual functions, modules, methods or classes
within a program.



HTH

Mick.