Search the Google Groups for "conditional formatting" vba, here:
groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Let us know how you go with your code.

Here is a site - He has a download file for conditional formating -
You could have almost unlimited conditions.

Have it set up in a sheet and it works GREAT.

http://xl-logic.com/

He has done a nice job here.

Something like this will work...

Option Explicit

Sub Conditional_Shading()
Dim cl As Range
Dim RNG As Range
'get the last used cell of column  q
Set RNG = Columns("q").Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows)
For Each cl In Range([Q1], [RNG])
If cl > 0 And cl <= 5 Then
With Cells(cl.Row, 1).Interior
Cells(cl.Row, 1).Select
Select Case cl.Value
Case Is = 5
.ColorIndex = 5 'Blue
Case Is = 4
.ColorIndex = 4 'Green
Case Is = 2
.ColorIndex = 3 'Red
Case Is = 1
.ColorIndex = 7 'Pink
End Select
'.Pattern = xlSolid
.Pattern = xlGray25
End With
End If
Next cl
End Sub