Comparing Two Worksheets in Excel

A user came to me today needing to quickly compare two Excel worksheets which were supposed to be the same but were not. Because she needed a very quick answer, I did a quick Google search and found a nice macro which grabbed Sheet1, compared it to Sheet2, and then created a new workbook. In the new workbook, any changed cell had both the original text and the changed text. Now, I like to give credit where credit is due. I found the original code for the macro here.

The problem was that the new workbook contained over 5000 changed cells. Some of the cells had a text paragraph in them originally, so the new workbook contained cells with huge amounts of text separated by "<>". It was a visual nightmare, as the user had a hard time quickly telling where the original stopped and the changed started. I requested some help, and nutsch from Experts-Exchange came to my rescue. He came up with a quick macro which changed the color of the text after the separator mark (<>) to red.

Sub ChangeColor()
Dim cl As range

On Error Resume
Next

For Each cl In ActiveSheet.UsedRange.Cells

If Len(Trim$(cl)) > 0 Then
If InStr(cl, "<>") > 0 Then _
cl.Characters(Start:=InStr(cl, "<>") + 2, Length:=Len(cl)).Font.ColorIndex = 3
End If
Next

End Sub

Here is the question on Experts-Exchange which resulted in this code:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25155367.html

After presenting this solution to the user, she again wanted something more. This time, she said it would be better to just highlight the changes in Sheet2 in the original Workbook rather than concatenating the two respective cells when a change existed. I once again turned to the Experts, and nutsch once again came to my rescue. This code simply changes the text color in Sheet2 to red if it is different than the text in Sheet1.

Sub TrackDifferences()
Dim rng As range, cl As range, sht2 As
Worksheet, sht1 As Worksheet

Set sht2 = Sheets("Sheet2")
Set sht1 = Sheets("Sheet1")

Set rng = sht1.UsedRange 'set variable rng to the range selected

application.ScreenUpdating = False

For Each cl In rng.Cells 'loop through all cells of the selected range
If sht2.Cells(cl.Row, cl.Column) <> cl Then _
sht2.Cells(cl.Row, cl.Column).Font.ColorIndex = 3
Next

application.ScreenUpdating = True
End Sub

Here is the Question on Experts-Exchange which resulted in this code:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25155502.html

In the end, the user had two different options: Option 1 was a new Workbook which had both old and new, and Option 2 just highlighted the changes directly in Sheet2. I hope that someone can benefit from this comparison utility.

Justin

Outlook HTML Signature Misaligned on Reply or Forward

Occasionally I run across a Question from someone that is truly unique and cannot find any references to it. This happened to me when someone mentioned a problem with an Outlook signature when using HTML formatting. A user had an email signature which was causing replies and forwards to be misaligned. The forwarded or replied message appeared next to the signature instead of below it. See the attached image for an example of what I mean.

It was reproducible across profiles, operating systems (XP, Vista, and 7), and versions of Outlook (2003 and 2007). The signature worked fine on an original message. It was a table which contained a graphic in the left column and demographic information of the sender in the right column. No matter how I manipulated the code, it retained the forward and reply misalignment. Finally, I removed the Table, and the problem when away. After trying several variations, I discovered that Outlook simply does not like to have a table in the signature file.

I have been unable to locate any documentation about this on Microsoft’s website at all, though it may be there. So, if you are an Outlook user who has had problems with your signature file on replies and forwards, check to make sure that it does not have a table in it. It may save you hours of frustration.

The Question which prompted this Article can be found here:


http://www.experts-exchange.com/Microsoft/Applications/Q_24989492.html


Justin

SCOM Across Multiple Domains

I recently ran into a question where someone wanted to deploy SCOM in two different domains. The problem was that the two sites they used were connected two domains, and while they trusted each other, they were not in the same forest. The person asking the question wanted to install the remote agent at his other site, but the installation was failing.

The solution to the question was to deploy a gateway server at the remote site as described in this Technet Article:

http://technet.microsoft.com/en-us/library/bb432149.aspx

The procedural overview as laid out in that article is to:

1. Request certificates for any computer in the agent, gateway server, management server chain.
2. Import those certificates into the target computers by using the Operations Manager 2007 MOMCertImport.exe tool.
3. Distribute the Microsoft.EnterpriseManagement.gatewayApprovalTool.exe to the management server.
4. Run the Microsoft.EnterpriseManagement.GatewayApprovalTool.exe tool to initiate communication between the management server and the gateway.
5. Install the gateway server.

The detailed directions for each step are laid out there in a simple to understand fashion.
This is actually a very good solution for conditions which require Cross Forest SCOM deployments. While my personal preference would be to bring both domains into a single forest, there are many reasons (mostly legal or political) to not do so. In the event you find yourself needing to have a single management point for multiple domains, this is the way I would go.

Here is the Experts-Exchange Question that prompted me to find this solution:

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/2003_Server/Q_25083584.html

Here is the Experts-Exchange Article which resulted from this Question:

http://www.experts-exchange.com/articles/Software/Server_Software/File_Servers/Active_Directory/SCOM-Across-Trusted-Domains-in-Multiple-Forests.html

Justin