Retain Conditional Format When Condition Is No Longer Valid

R

Rob

I would like to compare range A to range B net out differences using
conditional formatting - differences shown in bold red font. How do I retain
range A with the result format when range B is deleted? I'm using Excel
2003. Thanks!
 
R

rdwj

not with conditional formatting !
When you delete B, the condition disappears, hence your formatting disappears.

What you can do is create a simple VB macro that formats A based on B. In
that way it becomes independent from B and hence if you delete B the format
stays.

I suggest that you record a macro to record the kind of formatting you want
in cell A, the edit the macro. If you need more help, let us know.
RDWJ (also Rob...)
 
R

Rob

Rob - thanks! Ugh...macros...not one of my strengths! I'm able to write the
formatting piece - but I could use a little help with editing it to compare.
Can you assist? Thanks!!
 
R

rdwj

Sub Set_Formatting()

'set start points
Range("A1").Select

'Create loop, as long as the cell contains an entry
While ActiveCell <> ""

'create function that determines weather to format or not
' in this case if the cell to the right > 3
If ActiveCell.Offset(0, 1) > 3 Then

'apply formatting, in this case yellow background
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If

'go to the next cell (one cell down)
ActiveCell.Offset(1, 0).Range("A1").Select

'go back to loop
Wend
End Sub
 
R

Rob

Rob - thanks - I really appreciate your help with this! We're getting
closer. See example below - I need to compare A1 to A5, then A2 to A6, and
so on..then do the same for the remaining cols. Cell B6 need to change to
bold red font as a result.

A B C
1 1 2 3
2 4 5 6
3 7 8 9
4
5 1 2 3
6 4 20 6
7 7 8 9
 
R

rdwj

Sub Set_Formatting()

'set start points
Range("b6").Select

'Create loop, as long as the cell contains an entry
While ActiveCell <> ""

'create second loop for columns
While ActiveCell <> ""

'create function that determines weather to format or not
' in this case if the cell 4 up > cell value
If ActiveCell.Offset(-4, 0) <> ActiveCell Then

'apply formatting, in this case yellow background
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
End If

'go to the next cell (one cell down)
ActiveCell.Offset(1, 0).Range("A1").Select
'go back to loop
Wend
'go to the next column (row 6, column +1)
Cells(6, ActiveCell.Column + 1).Select
Wend
End Sub
 
R

Rob

Rob - THANK YOU!!! THANK YOU!!! You just eliminated several hours of
blinding comparisons I do a month!!!
 
Top