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 / Excel / Programming / 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 02:14 GMT
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!

Here's the sort of thing I'm trying, in a Word-based macro:

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
Beverly - 04 Mar 2008 02:48 GMT
Hi Steve,

Have you tried using conditional formatting?  You don't have to write any
code for this.  Just go to cell C1, select Format-Conditional Formatting,
select cell value is not equal to B1, then click the "Format" button and
change it to something like a red background with bold font.

Beverly

> 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
[quoted text clipped - 39 lines]
>     Next
> End Sub
steve.breslin@gmail.com - 04 Mar 2008 03:07 GMT
Hi Beverly,

> Have you tried using conditional formatting?  You don't have to write any
> code for this.  Just go to cell C1, select Format-Conditional Formatting,
> select cell value is not equal to B1, then click the "Format" button and
> change it to something like a red background with bold font.

That works, but I need to do this for every single row in a very long
table. Is there a way to generalize the rule so it covers all the
rows, not just one-at-a-time?

Thanks!
Gord Dibben - 04 Mar 2008 05:17 GMT
steve

Select column C and Format>CF>Formula is:

=C1<>B1

Format to a color and OK your way out.

The C1 and B1 references, being relative, will increment down the column.

Gord Dibben  MS Excel MVP

>Hi Beverly,
>
[quoted text clipped - 8 lines]
>
>Thanks!
Gary Keramidas - 04 Mar 2008 03:25 GMT
see if something like this would work

Option Explicit

Sub compaer_cells()
Dim i As Long
Dim lastrow As Long
Dim ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row

For i = 1 To lastrow
If ws.Range("B" & i).Value <> ws.Range("C" & i).Value Then
     ws.Range("C" & i).Interior.ColorIndex = 35
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Signature

Gary

>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
[quoted text clipped - 39 lines]
>    Next
> End Sub
steve.breslin@gmail.com - 04 Mar 2008 12:14 GMT
That works wonderfully, Gary, thanks very kindly. Thanks also to
Beverly and Gord -- I got this solution to work, but Excel is a bit
clumsy with this, e.g., coloring only bullets rather than the whole
contents of a cell, when it finds bullets.

By the way, what's the best way to become competent with this kind of
stuff for Word and Excel? Is one of the books I've seen considered
better than the rest? Or is there a particularly good in-depth online
tutorial?
 
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.