macros

Tyre · Mar 13th, 2002, 06:35 AM

is there a way to associate a number with a color?
for example:
when the number 1 is pressed, the cell color becoms yellow, when a 2 is pressed the cell becomes pink?

[ This Message was edited by: Tyre on 2002-03-13 05:35 ]

Mudface · Mar 13th, 2002, 07:04 AM

The following should work OK, paste it into the worksheet, it turns the font colour white, unless the cell colour is white, adapt as necessary

:-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Count > 1 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub

Select Case Target.Value
Case 1 To 56
Target.Interior.ColorIndex = Target.Value
If Target.Value <> 2 Then _
Target.Font.ColorIndex = 2
End Select

End Sub

Tyre · Mar 14th, 2002, 07:05 AM

couldn't make the macro work, ended up using Conditional Formatting
thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 06:35 AM	#1
Tyre Board Regular Join Date: Feb 2002 Location: Regina, Canada Posts: 79	is there a way to associate a number with a color? for example: when the number 1 is pressed, the cell color becoms yellow, when a 2 is pressed the cell becomes pink? [ This Message was edited by: Tyre on 2002-03-13 05:35 ]

Mar 13th, 2002, 07:04 AM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	The following should work OK, paste it into the worksheet, it turns the font colour white, unless the cell colour is white, adapt as necessary :- Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Count > 1 Then Exit Sub If Not IsNumeric(Target.Value) Then Exit Sub Select Case Target.Value Case 1 To 56 Target.Interior.ColorIndex = Target.Value If Target.Value <> 2 Then _ Target.Font.ColorIndex = 2 End Select End Sub

Mar 14th, 2002, 07:05 AM	#3
Tyre Board Regular Join Date: Feb 2002 Location: Regina, Canada Posts: 79	couldn't make the macro work, ended up using Conditional Formatting thanks