Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formating
Currnetly you can conditional format at cell for up to 3 conditions
Example under 40=red, 41 to 100 = blue and over 101= orange Is there a way to add a fouth condition? Say under 39 = green Thanks -- Norton Professional 2004 says this email is clean...believe it |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formating
Hi
conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany PCOR wrote: Currnetly you can conditional format at cell for up to 3 conditions Example under 40=red, 41 to 100 = blue and over 101= orange Is there a way to add a fouth condition? Say under 39 = green Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formating
Thanks for the quick response
The first part of your answer works very well to change the font colors. I am afraid that I do not understand the code you have under WORKSHEET_change The first line "If Target.Cells.Count 1 Then Exit Sub" seems to imply that is the cell contains a number greater than 1, then exit the sub If that is the right way of reading that statement, I don't see how the rest of the code will ever be executed I sure hope that you can help me with this problem Thanks Ian M "Frank Kabel" wrote in message ... Hi conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany PCOR wrote: Currnetly you can conditional format at cell for up to 3 conditions Example under 40=red, 41 to 100 = blue and over 101= orange Is there a way to add a fouth condition? Say under 39 = green Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formating
Hi
the first VBA statement just checks that only ONE cell is changed (and not mutiple cells at the same time) -- Regards Frank Kabel Frankfurt, Germany PCOR wrote: Thanks for the quick response The first part of your answer works very well to change the font colors. I am afraid that I do not understand the code you have under WORKSHEET_change The first line "If Target.Cells.Count 1 Then Exit Sub" seems to imply that is the cell contains a number greater than 1, then exit the sub If that is the right way of reading that statement, I don't see how the rest of the code will ever be executed I sure hope that you can help me with this problem Thanks Ian M "Frank Kabel" wrote in message ... Hi conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany PCOR wrote: Currnetly you can conditional format at cell for up to 3 conditions Example under 40=red, 41 to 100 = blue and over 101= orange Is there a way to add a fouth condition? Say under 39 = green Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formating
Hi again...I must be very slow today as I can not make that work
Could/would you please show me the required code for there following. if I enter a value under 20 I want that cell background to turn red and if it is 20 or over the cell would turn blue. Many thanks in advance. Ian M "Frank Kabel" wrote in message ... Hi the first VBA statement just checks that only ONE cell is changed (and not mutiple cells at the same time) -- Regards Frank Kabel Frankfurt, Germany PCOR wrote: Thanks for the quick response The first part of your answer works very well to change the font colors. I am afraid that I do not understand the code you have under WORKSHEET_change The first line "If Target.Cells.Count 1 Then Exit Sub" seems to imply that is the cell contains a number greater than 1, then exit the sub If that is the right way of reading that statement, I don't see how the rest of the code will ever be executed I sure hope that you can help me with this problem Thanks Ian M "Frank Kabel" wrote in message ... Hi conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany PCOR wrote: Currnetly you can conditional format at cell for up to 3 conditions Example under 40=red, 41 to 100 = blue and over 101= orange Is there a way to add a fouth condition? Say under 39 = green Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formating
No, it means that if the range that triggered the call to the function
consists of more than one cell, then abort. The function could be modified to remove that line and in the remaining code replace Target with Target.Cells(1). On Wed, 28 Jul 2004 21:38:08 GMT, "PCOR" wrote: Thanks for the quick response The first part of your answer works very well to change the font colors. I am afraid that I do not understand the code you have under WORKSHEET_change The first line "If Target.Cells.Count 1 Then Exit Sub" seems to imply that is the cell contains a number greater than 1, then exit the sub If that is the right way of reading that statement, I don't see how the rest of the code will ever be executed I sure hope that you can help me with this problem Thanks Ian M "Frank Kabel" wrote in message ... Hi conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany PCOR wrote: Currnetly you can conditional format at cell for up to 3 conditions Example under 40=red, 41 to 100 = blue and over 101= orange Is there a way to add a fouth condition? Say under 39 = green Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formating
The only way to get a fourth is to set the cells to that colour as default,
and any that match the CF conditions will assume one of the other three. -- HTH ------- Bob Phillips "PCOR" wrote in message gers.com... Currnetly you can conditional format at cell for up to 3 conditions Example under 40=red, 41 to 100 = blue and over 101= orange Is there a way to add a fouth condition? Say under 39 = green Thanks -- Norton Professional 2004 says this email is clean...believe it |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating | Excel Worksheet Functions | |||
Conditional Formating | Excel Discussion (Misc queries) | |||
conditional formating - Help | Excel Worksheet Functions | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
Conditional Formating | Excel Discussion (Misc queries) |