If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting
In Excel 97, is it possible to have more than 4
conditional formats? Maybe up to 6 or 7. I am looking at changing the color of a row (say columns A through H) based on the value in column H and is a date format. An example would be: - If the date in H1 is past today, cells A1 through H1 are Red. - If the date in H1 is between tomarrow and 7 days from today, cells A1 through H1 are Blue. - If the date in H1 is between 8 days from today and 14 days from today, cells A1 through H1 are Yellow. - If the date in H1 is between 15 days from today and 21 days from today, cells A1 through H1 are green. - If the date in H1 is between 22 days from today and 28 days from today, cells A1 through H1 are magenta. - If the date in H1 is greater than 28 days from today, cells A1 through H1 are White. |
#2
|
|||
|
|||
Conditional Formatting
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 Art wrote: In Excel 97, is it possible to have more than 4 conditional formats? Maybe up to 6 or 7. I am looking at changing the color of a row (say columns A through H) based on the value in column H and is a date format. An example would be: - If the date in H1 is past today, cells A1 through H1 are Red. - If the date in H1 is between tomarrow and 7 days from today, cells A1 through H1 are Blue. - If the date in H1 is between 8 days from today and 14 days from today, cells A1 through H1 are Yellow. - If the date in H1 is between 15 days from today and 21 days from today, cells A1 through H1 are green. - If the date in H1 is between 22 days from today and 28 days from today, cells A1 through H1 are magenta. - If the date in H1 is greater than 28 days from today, cells A1 through H1 are White. |
#3
|
|||
|
|||
Conditional Formatting
Thank you!
I will try this. If I want to do this or 400 rows of data, I trust I will need to include this VBA code 400 times. Is there an easier way? -----Original 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 Art wrote: In Excel 97, is it possible to have more than 4 conditional formats? Maybe up to 6 or 7. I am looking at changing the color of a row (say columns A through H) based on the value in column H and is a date format. An example would be: - If the date in H1 is past today, cells A1 through H1 are Red. - If the date in H1 is between tomarrow and 7 days from today, cells A1 through H1 are Blue. - If the date in H1 is between 8 days from today and 14 days from today, cells A1 through H1 are Yellow. - If the date in H1 is between 15 days from today and 21 days from today, cells A1 through H1 are green. - If the date in H1 is between 22 days from today and 28 days from today, cells A1 through H1 are magenta. - If the date in H1 is greater than 28 days from today, cells A1 through H1 are White. . |
#4
|
|||
|
|||
Conditional Formatting
You can only have one Worksheet_Change macro per sheet
Try: Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Then Exit Sub If .Column = 1 Then Select Case .Value Case "Red" .Interior.ColorIndex = 3 Case "Blue" .Interior.ColorIndex = 10 'etc Case Else .Interior.ColorIndex = xlColorIndexNone End Select End If End With End Sub which will work for any cell in column A. The Case Else is included to set the color back to the default if the cell's value is changed after first setting the color. Note that since changing the .interior.colorindex property doesn't cause any events to fire, there's no need to set .EnableEvents to False, and therefore no need for the extra overhead of the On Error GoTo code. In article , "Art" wrote: I will try this. If I want to do this or 400 rows of data, I trust I will need to include this VBA code 400 times. Is there an easier way? |
#5
|
|||
|
|||
Conditional Formatting
Sorry - I looked at the example that Frank gave rather than the original
Post. This is more appropriate: Private Sub Worksheet_Change(ByVal Target As Range) Dim nColorIndex As Long With Target If .Count 1 Then Exit Sub If .Column = 8 Then 'column H If IsDate(.Value) Then Select Case .Value - Date Case Is 28 'White nColorIndex = 2 Case Is 21 'Magenta? nColorIndex = 7 Case Is 14 'Green nColorIndex = 10 Case Is 7 'Yellow nColorIndex = 6 Case Is 0 'Red nColorIndex = 3 Case Else nColorIndex = xlColorIndexNone End Select Cells(.Row, 1).Resize( _ 1, 8).Interior.ColorIndex = nColorIndex End If End If End With End Sub In article , JE McGimpsey wrote: You can only have one Worksheet_Change macro per sheet Try: Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Then Exit Sub If .Column = 1 Then Select Case .Value Case "Red" .Interior.ColorIndex = 3 Case "Blue" .Interior.ColorIndex = 10 'etc Case Else .Interior.ColorIndex = xlColorIndexNone End Select End If End With End Sub which will work for any cell in column A. The Case Else is included to set the color back to the default if the cell's value is changed after first setting the color. Note that since changing the .interior.colorindex property doesn't cause any events to fire, there's no need to set .EnableEvents to False, and therefore no need for the extra overhead of the On Error GoTo code. In article , "Art" wrote: I will try this. If I want to do this or 400 rows of data, I trust I will need to include this VBA code 400 times. Is there an easier way? |
Thread Tools | |
Display Modes | |
|
|