Case Select

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello
I am trying to modify existing code

On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Me.Range("F2:IV2")
Select Case UCase(cell.Value)
Case "Cat": ColorCell UCase(cell.Value), cell, 39
Case "Dog": ColorCell UCase(cell.Value), cell, 40
Case "Fish": ColorCell UCase(cell.Value), cell, 35
Case "Horse": ColorCell UCase(cell.Value), cell, 36
End Select
Next
WS_exit

The cases will be the same Cat,Dog,Fish,Horse.
However
if F1:IV1 = "CAR" me.range is F2:IV2 and
if F1:IV1= Numeric then me.range is F3:IV3

Thanks for your help
Kelly
 
Kelly said:
Hello
I am trying to modify existing code

On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Me.Range("F2:IV2")
Select Case UCase(cell.Value)
Case "Cat": ColorCell UCase(cell.Value), cell, 39
Case "Dog": ColorCell UCase(cell.Value), cell, 40
Case "Fish": ColorCell UCase(cell.Value), cell, 35
Case "Horse": ColorCell UCase(cell.Value), cell, 36
End Select
Next
WS_exit

The cases will be the same Cat,Dog,Fish,Horse.
However
if F1:IV1 = "CAR" me.range is F2:IV2 and
if F1:IV1= Numeric then me.range is F3:IV3
Click to expand...

I am not sure what that means. Does F1:IV1 ="CAR" mean that every cell is
that value?
 
Kelly
on error goto ws_exit:
application.enableevents = false
dim merange as range
set merange = me.range("F2:IV2")
if range("F1:IV1").value="CAR" then set _
merange= me.range("F2:IV2")
If Application.WorksheetFunction.IsNumber(range("F1:IV1).value) _
= True _
then set merange = me.range("F3:IV3").value
For Each cell In merange
Select Case UCase(cell.Value)
Case "Cat": ColorCell UCase(cell.Value), cell, 39
Case "Dog": ColorCell UCase(cell.Value), cell, 40
Case "Fish": ColorCell UCase(cell.Value), cell, 35
Case "Horse": ColorCell UCase(cell.Value), cell, 36
End Select
Next
WS_exit
 
As bob phillips mentioned the code i posted will only return the value for
the upper left cell in the range it is checking, NOT the entire range
 
Yes,The value in F1:IV1 will either be "CAR" or a numeric value.
Thanks for your help!
kelly
 
So do you want it to use the alternate range if there is (at least) one
instance of CAR, or every cell is CAR?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
HI Bob
After a good nights sleep and reading your post and studying this worksheet
further this is what I have determined I need to change:
the first criteria is whether F2:IV2 is numeric or not. IF numeric the case
select should be based on F3:IV3. If F2:IV2="CAR" then the case select
should be based on F4:IV4. The ColorCell function then colors Row2:17 based
on the case select. So, if f2=1 and F3=Cat then F2:F17 cell color is 39 and
if g2=Car and F4 =dog, then F2:F17 cell color is 40.
This is slightly different from the original posting but I think i more
clearly illustrates what I am trying to accomplish
Thanks for your help!
K
 
Back
Top