Private Sub Worksheet_Calculate()

Dim icolor As Integer

for each cell in Range("P3:W3000")


Select Case cell

Case 1 To 50
icolor = 35

Case 51 To 100
icolor = 36

Case Is = 101
icolor = 3

Case Else
icolor = xlNone

End Select

cell.Interior.ColorIndex = icolor

End If

End Sub

Change P3:W3000 to reflect the range where you want the conditional coloring
applied. Also note that you can get up to 3 conditions (plus the default) in
the built in conditional formatting option under the format menu.

--
Regards,
Tom Ogilvy

"Mark" wrote:

Can someone tell me please how I can do the following, I am trying to
conditionally format a range of cells which sum from others.

I have used the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim icolor As Integer

If Intersect(Target, Range("P3:W3000")) Is Nothing Then


Select Case Target

Case 1 To 50
icolor = 35

Case 51 To 100
icolor = 36

Case Is = 101
icolor = 3

Case Else
'icolor = 0

End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

but this changes the cell that I am working on and not the range in the
target which is summing.

Please can someone provide me with a solution?

Many thanks

--
Mark