Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 95
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 95
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 95
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Menu Option No Longer Valid Mike H. Excel Discussion (Misc queries) 2 November 30th 07 12:05 AM
Conditional Format to highlight entire row if a condition is met klmiura Excel Worksheet Functions 5 November 22nd 07 03:30 PM
use more than 3 condition in conditional format Montu Excel Worksheet Functions 2 November 20th 07 10:51 AM
Conditional Format to Retain Text Colour from a List on another Sh Dasha New Users to Excel 9 November 5th 07 11:11 PM
How to valid the date with given condition? Eric Excel Discussion (Misc queries) 1 August 17th 07 04:33 AM


All times are GMT +1. The time now is 10:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"