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=0B8VP5...nload&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

On Aug 13, 3:45*pm, Joshua Houck <joshaho...@gmail.com> wrote:
> 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

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)

On Aug 13, 6:28*pm, Joshua Houck <joshaho...@gmail.com> wrote:
> 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

On Aug 13, 4:46*pm, Don Guillett <dguille...@gmail.com> wrote:
> On Aug 13, 6:28*pm, Joshua Houck <joshaho...@gmail.com> wrote:
>
> > 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

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=0B8V...5ZmU1&hl=en_US

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" <(E-Mail Removed)>
wrote in message
news:b59ef197-a767-4554-944e-(E-Mail Removed)...

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=0B8V...5ZmU1&hl=en_US

On Aug 13, 7:18*pm, "Jim Cone" <james.cone...@comcast.netXxx> wrote:
> 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" <joshaho...@gmail.com>
> wrote in messagenews:b59ef197-a767-4554-944e-(E-Mail Removed)...
>
> 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=0B8V...ItNThmMS00NDRj...

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

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" <(E-Mail Removed)>
wrote in message
news:0e79f2a4-f675-482d-8150-(E-Mail Removed)...
On Aug 13, 7:18 pm, "Jim Cone" <james.cone...@comcast.netXxx> wrote:
> 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" <joshaho...@gmail.com>
> wrote in messagenews:b59ef197-a767-4554-944e-(E-Mail Removed)...
>
> 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=0B8V...ItNThmMS00NDRj...

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

In message <j27bae$gi3$(E-Mail Removed)> of Sat, 13 Aug 2011 19:18:33 in
microsoft.public.excel.programming, Jim Cone
<(E-Mail Removed)> writes
>
>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.
--
Walter Briscoe

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" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
> In message <j27bae$gi3$(E-Mail Removed)> of Sat, 13 Aug 2011 19:18:33 in
> microsoft.public.excel.programming, Jim Cone
> <(E-Mail Removed)> writes
>>
>>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.
> --
> Walter Briscoe