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?