Case Select

[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

 

[Bob Phillips]

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

I am not sure what that means. Does F1:IV1 ="CAR" mean that every cell is
that value?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Yes,The value in F1:IV1 will either be "CAR" or a numeric value.
Thanks for your help!
kelly

 

[Bob Phillips]

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)

 

[Guest]

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