Conditional Formatting using VBA for multiple conditions

Within Excel 2003, I have created a table of 7 columns and 10 rows. Column 7 represents the sum of the first 6 colomns at each row level. I need to apply a background colour in column 7 (H1:H10) which changes dependant on the summed value.

Please see the attched sheet.

To achieve this I have followed this example ….

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("G3:G13")) is Nothing Then

Select Case Target

Case 0 To 9

icolor = 4

Case 10 To 19

icolor = 8

Case 20 To 29

icolor = 26

Case 30 To 49

icolor = 53

Case 21 To 25

icolor = 15

Case 50 To 999

icolor = 3

Case Else

'Whatever

End Select

Target.Interior.ColorIndex = icolor

End If


End Sub


However, this only works if the values of columns A to F have already been populated prior to creating the rule.

The ‘Pre Filled Data’ tab show the example of the A to F columns being completed prior to the creation of the rule. If I then change the values in columns A to D, the column E cell recalculates as per the formula but retains its original cell colour.

I also need this to work on a second table within the worksheet, using the same colour conditions.

Do I simply make the following amendment:

If Not Intersect(Target, Range("G3:G13", "O3:O13")) is Nothing Then

Thanks in advance for your help.