VP CODE

J

JLP

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?
 
B

Bob Phillips

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 said:
Since CF only lets you have 3 formats per cell can someone write how to
Click to expand...
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?
 
J

jlp

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?
 
B

Bob Phillips

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 said:
it comes up with an error an hi-lites the end with in the
Click to expand...
statement. thanks
 
J

jlp

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
 
B

Bob Phillips

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 said:
It works. Now next question. Can it be made to work if the cell is getting
Click to expand...
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
 
J

JLP

THIS DOESNT SEEM TO WORK. LETS SAY I TYPE red IN CELL A10, AND A1 = A10. SHOULD THIS WORK TO HAVE red COME UP IN A1 AND BE COLORED?
 
B

Bob Phillips

Should have tested it

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
Select Case LCase(.Value)
Case "red": .Offset(-9, 0).Interior.ColorIndex = 3
Case "blue": .Offset(-9, 0).Interior.ColorIndex = 5
Case "green": .Offset(-9, 0).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 said:
THIS DOESNT SEEM TO WORK. LETS SAY I TYPE red IN CELL A10, AND A1 =
Click to expand...
A10. SHOULD THIS WORK TO HAVE red COME UP IN A1 AND BE COLORED?
 
Top