Thanks Bob, that did the job!

"Bob Phillips" wrote in message
...
Try the calculate event

Private Sub Worksheet_Calculate()
Dim Target As Range
Dim iColor As Integer

For Each Target In Me.Range("B3:P16")
Select Case Target
Case 0 To 0.0001
iscolor = 2
Case 0.0001 To 0.2499
iColor = 3
Case 0.25 To 0.4999
iColor = 7
Case 0.5 To 0.6499
iColor = 6
Case 0.65 To 0.7499
iColor = 8
Case 0.75 To 1#
iColor = 4
Case Else
iColor = 2

End Select

Target.Interior.ColorIndex = iColor
End If

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Thyristor" <tknorris at plateautel dot net wrote in message
...
I have a need for conditional formatting based on greater than 6
conditions and have found VBA programming on http://www.ozgrid.com/V
(*thank you very much!*) and modified it slightly. It works perfectly for
the data manually entered in the cells in the Target Range. However, I am
trying to use the same conditional formatting on a calculated cell in the
Target Range and I can't get it to work the way I want. The calculated
cell will turn the color defined in the Case statement the first time it
is calculated but then it stays that color and will not reflect the
defined color for the the Case statement.

The data in the Target Range are percentages and here is the modified
code (row 16 cells are =Average(B3:B16), etc. and will not change color
per the case statement)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("B3:P16")) Is Nothing Then
Select Case Target
Case 0 To 0.0001
iscolor = 2
Case 0.0001 To 0.2499
icolor = 3
Case 0.25 To 0.4999
icolor = 7
Case 0.5 To 0.6499
icolor = 6
Case 0.65 To 0.7499
icolor = 8
Case 0.75 To 1#
icolor = 4
Case Else
icolor = 2

End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

Thanks, Tonly