Comparing cells, coloring differences
 

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

Comparing cells, coloring differences
 

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

" wrote:

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

Comparing cells, coloring differences
 

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!

Comparing cells, coloring differences
 

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

--


Gary


wrote in message
...
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

Comparing cells, coloring differences
 

steve

Select column C and FormatCFFormula 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

On Mon, 3 Mar 2008 19:07:24 -0800 (PST), wrote:

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!

Comparing cells, coloring differences
 

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?