VP CODE

[Guest]

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?

 

[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)

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?

 

[Guest]

thanks

 

[Guest]

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?

 

[Guest]

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

 

[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)

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

statement. thanks

 

[Guest]

thanks

 

[Guest]

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

 

[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)

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

 

[Guest]

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?

 

[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)

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?