I am using Data validation to put drop down lists in my
spreadsheet for about 30 different options. I want to
colour code each option for easy visual management. Is
there a way of creating a drop down list that not only
puts the text in but also the formatting.

The other way to do it is to use conditional formatting,
but i can only have 3 conditions. Can i use any VB code to
put a colour in a box dependent on what the text is, i.e.
if cell contents = two make cell background blue etc.

Any help is greatly appreciated as ever.

Danny.

Ypu can very easily just do this using conditional
formatting. However that does limit you to three
conditions.

You can code the CHANGE event in the worksheet for more
variety.

Paset th efollowing into a sheet's code page. Name a
range on the sheet MyData

Option Explicit

Const RED = 3
Const BLUE = 34
Const YELLOW = 36
Const PINK = 38
Const INDIGO = 24


Private Sub Worksheet_Change(ByVal target As Range)
Dim result As Range
Set result = Intersect(target, ThisWorkbook.Names
("MyData").RefersToRange)
If Not result Is Nothing Then

Formatter result

End If


End Sub

'''''''''''''''''
'''module-------

Sub Formatter(target As Range)
Dim MyColour As Long
Dim MyRange As Range
Set MyRange = ThisWorkbook.Names
("MyData").RefersToRange
Select Case UCase(target.Value2)

Case "A": MyColour = RED
Case "B": MyColour = BLUE
Case "C": MyColour = PINK
Case "D": MyColour = YELLOW
Case "E":: MyColour = INDIGO
Case Else: MyColour = 0
End Select
MyRange.Rows(target.Row - _
MyRange.Row + 1).Interior.ColorIndex = MyColour

End Sub


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I am using Data validation to put drop down lists in my
spreadsheet for about 30 different options. I want to
colour code each option for easy visual management. Is
there a way of creating a drop down list that not only
puts the text in but also the formatting.

The other way to do it is to use conditional formatting,
but i can only have 3 conditions. Can i use any VB code
to
put a colour in a box dependent on what the text is,
i.e.
if cell contents = two make cell background blue etc.

Any help is greatly appreciated as ever.

Danny.
.

This is the right idea, but a lot more complex than i
first thought. MY VB is not that good so can you explain
it for me please.

If i give an example as the following -

Spreadsheet name - Book1.xls
Worksheet name - Worksheet1
Data range A1:A5

The if A1 = Cod, A2 = Haddock, A3 = Mackeral, A4 = Shark,
A5 = Whiting

What i want is if the user types in Cod then the formula
makes this cell Blue, etc.

Could you possibly put the above example into your code so
i can see what i need to change to make it work for me.

Thanks,

Danny.







-----Original Message-----
Ypu can very easily just do this using conditional
formatting. However that does limit you to three
conditions.

You can code the CHANGE event in the worksheet for more
variety.

Paset th efollowing into a sheet's code page. Name a
range on the sheet MyData

Option Explicit

Const RED = 3
Const BLUE = 34
Const YELLOW = 36
Const PINK = 38
Const INDIGO = 24


Private Sub Worksheet_Change(ByVal target As Range)
Dim result As Range
Set result = Intersect(target, ThisWorkbook.Names
("MyData").RefersToRange)
If Not result Is Nothing Then

Formatter result

End If


End Sub

'''''''''''''''''
'''module-------

Sub Formatter(target As Range)
Dim MyColour As Long
Dim MyRange As Range
Set MyRange = ThisWorkbook.Names
("MyData").RefersToRange
Select Case UCase(target.Value2)

Case "A": MyColour = RED
Case "B": MyColour = BLUE
Case "C": MyColour = PINK
Case "D": MyColour = YELLOW
Case "E":: MyColour = INDIGO
Case Else: MyColour = 0
End Select
MyRange.Rows(target.Row - _
MyRange.Row + 1).Interior.ColorIndex = MyColour

End Sub


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I am using Data validation to put drop down lists in my
spreadsheet for about 30 different options. I want to
colour code each option for easy visual management. Is
there a way of creating a drop down list that not only
puts the text in but also the formatting.

The other way to do it is to use conditional formatting,
but i can only have 3 conditions. Can i use any VB code
to
put a colour in a box dependent on what the text is,
i.e.
if cell contents = two make cell background blue etc.

Any help is greatly appreciated as ever.

Danny.
.

.