Format colour of cells based on other cell - 34 colours

[Guest]

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?

 

[Norman Jones]

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.

 

[Simon Lloyd]

Try this by Bob Philips
http://www.developersdex.com/vb/message.asp?p=2677&r=5116812 you can
modify it by removing .EntireRow and adding a range or this one has
lots of worked examples and explanations
http://www.geocities.com/davemcritchie/excel/event.htm

Regards
Simon