Color cells x5

[jladika]

I would like to have when I put the number 1 in the cell for it to tur
red

in the cell for it to turn blue

in the cell for it to turn green

in the cell for it to turn yellow

in the cell for it to turn orange

please help me out

if a micro is needed please make on for me so i can copie it and past

thanks


Jo

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 5 'blue
Case 3: .Interior.ColorIndex = 10 'green
Case 4: .Interior.ColorIndex = 6 'yellow
Case 5: .Interior.ColorIndex = 46 'orange
End Select
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

RP
(remove nothere from the email address if mailing direct)

 

[swatsp0p]

Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
'Bruce Watson 10/27/2005
Static iCellColour As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Select Case .Cells.Value
Case 1: .Interior.ColorIndex = 3 'Red
Case 2: .Interior.ColorIndex = 5 'Blue
Case 3: .Interior.ColorIndex = 10 'Green
Case 4: .Interior.ColorIndex = 6 'Yellow
Case 5: .Interior.ColorIndex = 46 'Orange (use 7 for pink)
Case Else: .Interior.ColorIndex = 0
End Select

End With

End If

ws_exit:
Application.EnableEvents = True
End Sub

Note: the Range in the 6th line of the code. Adjust this range to
match the desired range in your sheet.

Another note: Orange and Red look very similar (at least on my
display), maybe use Pink (7).

Final note: This is a worksheet_change event and needs to be placed in
the Sheet module, not a regular module. Right click on the sheet tab
and select "View Code", paste this code in the window that opens.

Good Luck