Hi Blain,

Try something like:

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

Set rng = Me.Range("A1") '<<==== CHANGE

If Not Intersect(rng, Target) Is Nothing Then
With rng
Select Case UCase(.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
End Select
End With
End If

End Sub
'<<=============

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



"blain" wrote in
message ...

I want to be able to change the background colour of a cell depending on
its content, I could use conditional formatting but I have more than
three conditions.

I have 14 different conditions.

Can someone provide me a generic VBA code to accomplish this.

The content is text rather than a number.

Hope someone can help.


--
blain
------------------------------------------------------------------------
blain's Profile:
http://www.excelforum.com/member.php...o&userid=34368
View this thread: http://www.excelforum.com/showthread...hreadid=552591

Hi Blain,

Replace:

Case "NORA": .Interior.ColorIndex = 16

with
Case "NORA": .Interior.ColorIndex = 16
Case Else: .Interior.ColorIndex = xlNone

---
Regards,
Norman