Since CF only lets you have 3 formats per cell can someone write how to put it in code to have 10 different backround shadings based on certain text names in a group of cells. Also what are all the options in the worksheet section? Which one do I write the code under? Will this then work automatically for me

Can you email me with a file that will do this?

Here is an example with just 3 colours but it can easily be extended by
extra case statements

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Select Case LCase(.Value)
Case "red": .Interior.ColorIndex = 3
Case "blue": .Interior.ColorIndex = 5
Case "green": .Interior.ColorIndex = 10
'etc
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JLP" wrote in message
...
Since CF only lets you have 3 formats per cell can someone write how to
put it in code to have 10 different backround shadings based on certain text
names in a group of cells. Also what are all the options in the worksheet
section? Which one do I write the code under? Will this then work
automatically for me?

Can you email me with a file that will do this?

thanks

thanks To the right of the worksheet setting there is another pulldown with different options, (like activate, calculate, change, etc.) which one of these does it go under?

it comes up with an error an hi-lites the end with in the statement. thanks

Missed a line

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Select Case LCase(.Value)
Case "red": .Interior.ColorIndex = 3
Case "blue": .Interior.ColorIndex = 5
Case "green": .Interior.ColorIndex = 10
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jlp" wrote in message
...
it comes up with an error an hi-lites the end with in the
statement. thanks

thanks

It works. Now next question. Can it be made to work if the cell is getting the information (say the case red) from another cell? Say cell a1= cell a10 (where a10 is red). a1 then doesnt color. In this instance it will not work. Thanks

yeah, that's easy enough as long as they are consistently linked. By this I
mean, say the text is entered in A10:H10, then the cells A1:H1 get coloured,
simply use

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A10:H10")) Is Nothing Then
With Target.Offset(-9,0)
Select Case LCase(.Value)
Case "red": .Interior.ColorIndex = 3
Case "blue": .Interior.ColorIndex = 5
Case "green": .Interior.ColorIndex = 10
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jlp" wrote in message
...
It works. Now next question. Can it be made to work if the cell is getting
the information (say the case red) from another cell? Say cell a1= cell a10
(where a10 is red). a1 then doesnt color. In this instance it will not
work. Thanks