Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100") 'adjust to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(1, 2, 3, 4, 5, 6, 7) 'cell values
nums = Array(8, 9, 6, 3, 7, 4, 20) 'colorindex numbers
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor 0 Then
rr.EntireRow.Interior.ColorIndex = icolor
End If
Next
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that module.

Edit to suit. Alt + q to return to Excel.


Gord Dibben MS Excel MVP

On Thu, 14 Jan 2010 00:16:01 -0800, Peterepeat
wrote:

I have a table and in one column the user needs to add a number between 1-7
(inclusive) to show the status. Ideally I would then like the whole row to
be coloured, depending on the number inserted (eg. 1=Blue, 2=red, etc)

With the conditional formatting option within Excel I can only do 3 options.

How do I overcome this?