G
Guest
How do I get 1-5 entered as a value to format with each five numbers having
its own background color?
its own background color?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
Cindy said:How do I get 1-5 entered as a value to format with each five numbers having
its own background color?
P
Peo Sjoblom
If you have excel 2007 you can use conditional formatting, earlier versions
would need VBA
http://www.xldynamic.com/source/xld.CFPlus.Download.html
would need VBA
http://www.xldynamic.com/source/xld.CFPlus.Download.html
G
Gord Dibben
You want this for one cell with a choice of 5 numbers?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A1"))
' for a range use "A1:A10" or similar
On Error GoTo endit
Application.EnableEvents = False
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub
This is sheet event code. Right-click on the sheet tab and "View Code".
Copy/paste into that sheet module.
Gord Dibben MS Excel MVP
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A1"))
' for a range use "A1:A10" or similar
On Error GoTo endit
Application.EnableEvents = False
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub
This is sheet event code. Right-click on the sheet tab and "View Code".
Copy/paste into that sheet module.
Gord Dibben MS Excel MVP
G
Guest
Thank you; this worked great!
Peo Sjoblom said:If you have excel 2007 you can use conditional formatting, earlier versions
would need VBA
http://www.xldynamic.com/source/xld.CFPlus.Download.html