Hi Graham,

Try something like:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rng2 As Range

Set rng = Me.Range("B1") '<<==== CHANGE
Set rng2 = Me.Range("A1") '<<==== CHANGE
Set rng = Intersect(rng, Target)

If Not rng Is Nothing Then
With rng2
Select Case UCase(rng.Value)
Case "ANNE": .Interior.ColorIndex = 3
Case "BEN": .Interior.ColorIndex = 4
Case "CAROL": .Interior.ColorIndex = 5
Case "DAVID": .Interior.ColorIndex = 6
Case "EWAN": .Interior.ColorIndex = 7
Case "FREDA": .Interior.ColorIndex = 8
Case "GRAHAM": .Interior.ColorIndex = 9
Case "HARRY": .Interior.ColorIndex = 10
Case "IAN": .Interior.ColorIndex = 11
Case "JANE": .Interior.ColorIndex = 12
Case "KATE": .Interior.ColorIndex = 13
Case "LEN": .Interior.ColorIndex = 14
Case "MARY": .Interior.ColorIndex = 15
Case "NORA": .Interior.ColorIndex = 16
Case Else: .Interior.ColorIndex = xlNone
End Select
End With
End If
End Sub
'<<=============

As written, the background colour of A1 changes in response to the value
inserted in B1.

Change the ranges and trigger values to suit.

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman


"Graham Tritton" wrote in message
...
I need to conditional format the BG colour of a cell based on another cell.
I
can deal with 4 colours but not 34 seperate colours. Any ideas?