Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing % Differences From in Pivot Tables | Excel Discussion (Misc queries) | |||
Comparing text in cells for differences | Excel Discussion (Misc queries) | |||
comparing 2 workbooks and highlighting differences | Excel Programming | |||
Comparing two data ranges for differences. | Excel Discussion (Misc queries) | |||
Comparing two columns and listing differences | Excel Programming |