L
Lotus123
I have pieced together this code from several sources:
Private Sub ComboBox1_change()
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("B2:B2")
Set rng = Range("F7:G7")
If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 1
rng.Interior.ColorIndex = 3
Case 2
rng.Interior.ColorIndex = 46
Case 3
rng.Interior.ColorIndex = 6
Case 4
rng.Interior.ColorIndex = 10
Case 5
rng.Interior.ColorIndex = 5
Case 6
rng.Interior.ColorIndex = 13
End Select
End If
End Sub
If I use Worksheet_change instead of ComboBox change, the spreadshee
updates color as expected when I manually change the value of cell B2.
However, I am trying to get the above code to work when I use a form
combobox to change the value of cell B2.
If you want to look at the actual spreadsheet I have uploaded it to:
http://home.swbell.net/nate-sus/excel/colors.xls
Thanks
Private Sub ComboBox1_change()
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("B2:B2")
Set rng = Range("F7:G7")
If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 1
rng.Interior.ColorIndex = 3
Case 2
rng.Interior.ColorIndex = 46
Case 3
rng.Interior.ColorIndex = 6
Case 4
rng.Interior.ColorIndex = 10
Case 5
rng.Interior.ColorIndex = 5
Case 6
rng.Interior.ColorIndex = 13
End Select
End If
End Sub
If I use Worksheet_change instead of ComboBox change, the spreadshee
updates color as expected when I manually change the value of cell B2.
However, I am trying to get the above code to work when I use a form
combobox to change the value of cell B2.
If you want to look at the actual spreadsheet I have uploaded it to:
http://home.swbell.net/nate-sus/excel/colors.xls
Thanks