Hi James,

Am Sat, 18 Jan 2014 16:36:47 -0800 (PST) schrieb :

I would also like to add conditional formatting to each cell in column H ( 0 to 5 = red, 6 to 10, yellow, 11 to 15 = blue, and = 16 would be green), so as the user enters values in column I through AP, the cell color in column H would reflect the total. Is it possible to add conditional formatting in the same manner as inserting the formula?

try follwing code in the code module of Sheet 5. When a cell in Range
I1:AP56 is changed, the result and the interior color in H changes:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("I1:AP56")) Is _
Nothing Then Exit Sub

Dim IColor As Long
Dim FColor As Long

With Cells(Target.Row, "H")
.Formula = "=SUM(I" & Target.Row & ":AP" & Target.Row & ")"

Select Case .Value
Case Is <= 5
IColor = vbRed
FColor = vbWhite
Case Is <= 10
IColor = vbYellow
FColor = xlAutomatic
Case Is <= 15
IColor = vbBlue
FColor = vbWhite
Case Else
IColor = vbGreen
FColor = xlAutomatic
End Select
.Interior.Color = IColor
.Font.Color = FColor
End With
End Sub




Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2