Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain Conditional Format When Condition Is No Longer Valid
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain Conditional Format When Condition Is No Longer Valid
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...) "Rob" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain Conditional Format When Condition Is No Longer Valid
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!! "rdwj" wrote: 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...) "Rob" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain Conditional Format When Condition Is No Longer Valid
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 "Rob" wrote: 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!! "rdwj" wrote: 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...) "Rob" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain Conditional Format When Condition Is No Longer Valid
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 "rdwj" wrote: 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 "Rob" wrote: 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!! "rdwj" wrote: 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...) "Rob" wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain Conditional Format When Condition Is No Longer Valid
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 "Rob" wrote: 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 "rdwj" wrote: 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 "Rob" wrote: 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!! "rdwj" wrote: 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...) "Rob" wrote: 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain Conditional Format When Condition Is No Longer Valid
Rob - THANK YOU!!! THANK YOU!!! You just eliminated several hours of
blinding comparisons I do a month!!! "rdwj" wrote: 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 "Rob" wrote: 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 "rdwj" wrote: 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 "Rob" wrote: 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!! "rdwj" wrote: 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...) "Rob" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Menu Option No Longer Valid | Excel Discussion (Misc queries) | |||
Conditional Format to highlight entire row if a condition is met | Excel Worksheet Functions | |||
use more than 3 condition in conditional format | Excel Worksheet Functions | |||
Conditional Format to Retain Text Colour from a List on another Sh | New Users to Excel | |||
How to valid the date with given condition? | Excel Discussion (Misc queries) |