Case Select


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
 
Ad

Advertisements

B

Bob Phillips

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
I am not sure what that means. Does F1:IV1 ="CAR" mean that every cell is
that value?
 
G

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
 
G

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
 
G

Guest

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

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)
 
Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Case Select 10
Select Case 0
Select Case 13
Select Case 6
Select Case 4
Select case or If then 3
Select Case 6
Select Case 5

Top