Prev: Lost hyperlinks Excell 2003-HELP
Next: insert an order number when another sheet opens
From: Isis on 25 Apr 2010 09:59
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
From: Bob Phillips on 25 Apr 2010 12:39
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" <isissoft(a)NOSPAMbtinternet.com> wrote in message
news:Xns9D6598A2DD29Cisissoft(a)193.202.122.117...
>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


From: Gord Dibben on 25 Apr 2010 13:17
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"
<bob.phillips(a)somewhere.com> 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

 | 
Pages: 1
Prev: Lost hyperlinks Excell 2003-HELP
Next: insert an order number when another sheet opens