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.

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.

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.

.

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?

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?