Claire,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.  I have written the code so that you only need to modify it slightly to set the
range, but to get different colors you will use a worksheet function on the sheet.

What you do need to do is insert a formula into the cells of column A that will return the desired
color code. To see the various colors, select A2:A57, type =ROW()-1 and press Ctrl-Enter.

Then all you need to do is enter a formula into cells in column A that will return your desired
color code....For example

=IF(AND(B2>3,B2<=4),10,15)

That will format the row using either color 10 or 15....

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Dim myC As Range
For Each myC In Range("A2", Cells(Rows.Count, 1).End(xlUp))
  If myC.Value <> "" Then
    Cells(myC.Row, 2).Interior.ColorIndex = myC.Value
  End If
Next myC
End Sub