Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Long Documents / March 2008

Tip: Looking for answers? Try searching our database.

Comparing cells, coloring differences

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
steve.breslin@gmail.com - 04 Mar 2008 00:19 GMT
Hopefully last question for a while -- I would like to compare
adjoining cells (B1 and C1, B2 and C2, etc.), and change the color of
the C cells which are not identical to their B counterparts.

It would save me an enormous amount of work, but unfortunately I'm not
quite at the point where I can work this sort of thing out by myself.
Any help would be greatly appreciated indeed!
Jay Freedman - 04 Mar 2008 00:54 GMT
>Hopefully last question for a while -- I would like to compare
>adjoining cells (B1 and C1, B2 and C2, etc.), and change the color of
[quoted text clipped - 3 lines]
>quite at the point where I can work this sort of thing out by myself.
>Any help would be greatly appreciated indeed!

I think this handles all the contingencies, but I might have missed something.
Give it a whirl on some real tables and see if it needs any tweaks. Of course,
you can replace wdColorRed with any other color that appeals to you.

Sub ColorDifferences()
   'compare adjoining cells (B1 and C1, B2 and C2, etc.),
   'and change the color of the C cells which are not
   'identical to their B counterparts
   
   Dim oTbl As Table
   Dim oRow As Row
   Dim numRow As Long
   
   If Not Selection.Information(wdWithInTable) Then
       MsgBox "Please put the cursor in a table first."
       Exit Sub
   End If
       
   Set oTbl = Selection.Tables(1)
   
   If Not oTbl.Uniform Then
       MsgBox "The macro can't deal with merged or split cells."
       Exit Sub
   End If
   
   For numRow = 1 To oTbl.Rows.Count
       Set oRow = oTbl.Rows(numRow)
       With oRow
           If Not .HeadingFormat Then
               If .Cells(2).Range.Text <> .Cells(3).Range.Text Then
                   .Cells(3).Shading.ForegroundPatternColor = wdColorRed
               End If
           End If
       End With
   Next
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.
steve.breslin@gmail.com - 04 Mar 2008 01:25 GMT
Thanks Jay! Unfortunately I'm getting a compile bug. (Probably I'm
doing something silly, or using the wrong version of Excel: Office XP
Professional, by the way.) The offending line is...

Dim oTbl As Table

... and the error is ...

Compile Error:
User-defined type not defined.
Jay Freedman - 04 Mar 2008 01:53 GMT
>Thanks Jay! Unfortunately I'm getting a compile bug. (Probably I'm
>doing something silly, or using the wrong version of Excel: Office XP
[quoted text clipped - 6 lines]
>Compile Error:
>User-defined type not defined.

The problem is that you posted in the Word newsgroup, so I wrote a macro that
manipulates a Word table. (Yeah, maybe that is the wrong version of Excel. ;-)

For an answer for Excel, post in the microsoft.public.excel.programming group.
Good luck!

--
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.
steve.breslin@gmail.com - 04 Mar 2008 02:11 GMT
> The problem is that you posted in the Word newsgroup, so I wrote a macro that
> manipulates a Word table. (Yeah, maybe that is the wrong version of Excel. ;-)

Oh my goodness! My apologies!
DeanH - 04 Mar 2008 13:05 GMT
In Excel you don't need a macro for this.
Use the Conditional Formatting, under Formatting menu.
Hope this helps.
DeanH

> > The problem is that you posted in the Word newsgroup, so I wrote a macro that
> > manipulates a Word table. (Yeah, maybe that is the wrong version of Excel. ;-)
>
> Oh my goodness! My apologies!

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.