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 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 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 wrote:
On Aug 13, 6:28*pm, Joshua Houck 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"
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=0B8V...5ZmU1&hl=en_US

On Aug 13, 7:18*pm, "Jim Cone" 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"
wrote in ...

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 of Sat, 13 Aug 2011 19:18:33 in
microsoft.public.excel.programming, Jim Cone
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