Coloring cells using VBA in excel
 

I have drop downs on some cells - i have the code below that is meant to
color the drop down cell once set by the user - which i does. However, I
want to be able to 'reset' all my drop downs to 'blank' and clear the
exisiting colorising - I have been using -Range("B4:J33").Value = ""- to
clear the cells, but that triggers an error in the colorising code below
- I get a runtime error 13 that points to the first Case "Dan" statement

Any help on achiving my aim would be great.

Thanks

Code below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("B4:J33")) Is Nothing Then
Select Case Target
Case "Dan"
icolor = 34
Case "John"
icolor = 35
Case "Rose"
icolor = 38
Case Else
icolor = 99
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

Coloring cells using VBA in excel
 

TRy

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Long
Dim cell As Range
If Not Intersect(Target, Range("B4:J33")) Is Nothing Then
For Each cell In Target
Select Case cell
Case "Dan"
icolor = 34
Case "John"
icolor = 35
Case "Rose"
icolor = 38
Case Else
icolor = 99
End Select
cell.Interior.ColorIndex = icolor
Next cell
End If
End Sub

--

HTH

Bob

"Isis" wrote in message
...
I have drop downs on some cells - i have the code below that is meant to
color the drop down cell once set by the user - which i does. However, I
want to be able to 'reset' all my drop downs to 'blank' and clear the
exisiting colorising - I have been using -Range("B4:J33").Value = ""- to
clear the cells, but that triggers an error in the colorising code below
- I get a runtime error 13 that points to the first Case "Dan" statement

Any help on achiving my aim would be great.

Thanks

Code below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("B4:J33")) Is Nothing Then
Select Case Target
Case "Dan"
icolor = 34
Case "John"
icolor = 35
Case "Rose"
icolor = 38
Case Else
icolor = 99
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

Coloring cells using VBA in excel
 

Bob

iColor = 99 throws an error in 2003

Change to anything <57 and OK


Gord

On Sun, 25 Apr 2010 17:39:40 +0100, "Bob Phillips"
wrote:

TRy

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Long
Dim cell As Range
If Not Intersect(Target, Range("B4:J33")) Is Nothing Then
For Each cell In Target
Select Case cell
Case "Dan"
icolor = 34
Case "John"
icolor = 35
Case "Rose"
icolor = 38
Case Else
icolor = 99
End Select
cell.Interior.ColorIndex = icolor
Next cell
End If
End Sub