Modify existing code to allow for multiple case statements

Guys

I have been working with EE members to create this code but it was advised that as my original request has been honoured that I should create a new case.

So what I would like to happen

The following i would like to happen the code needs to allow for multiple case statements

so where the code refers to Stanlow it needs to allow for variouse groups of amounts

ie  if the value is >1 < 20 then blue if the same is >20 < 40 then green  if the same is > 50 then red
Private Sub Worksheet_Change(ByVal Target As Range) 
 
 
Dim cel As Range 
For Each cel In Target 
    If Not Intersect(cel, Range("K:K,M:M")) Is Nothing Then 
        Select Case Range("M" & cel.Row).Value 
            Case "Stanlow" 
                If Range("K" & cel.Row).Value > 10 Then 
                    Range("K" & cel.Row).Interior.ColorIndex = 8 
                Else 
                    Range("K" & cel.Row).Interior.ColorIndex = xlColorIndexNone 
                End If 
            Case "Grangemouth" 
                If Range("K" & cel.Row).Value < 50 Then 
                    Range("K" & cel.Row).Interior.ColorIndex = 6 
                Else 
                    Range("K" & cel.Row).Interior.ColorIndex = xlColorIndexNone 
                End If 
        End Select 
    End If 
Next cel 
End Sub

Open in new window

DarrenJacksonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RunriggerCommented:
select case "value"
case "Stanlow"

select case "value2"
case "1"
case "2"
end select
case "Grangemouth"
blah
blah
end select
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RunriggerCommented:
when doing levels if thresholds, easier to test them in reverse or

Select Case "Value1"
case >50
---Red
case >40
---there is a gap in your test above (nothing between 40 and 50)?????
case >20
---Green
case else
---Blue
End select

0
Rory ArchibaldCommented:
Your question is not clear as regards where 20 would fall, but you can use ranges in case statements:

Private Sub Worksheet_Change(ByVal Target As Range) 
 
 
Dim cel As Range 
For Each cel In Target 
    If Not Intersect(cel, Range("K:K,M:M")) Is Nothing Then 
        Select Case Range("M" & cel.Row).Value 
            Case "Stanlow" 
                Select Case Range("K" & cel.Row).Value
                    case 1 to 20 
                       Range("K" & cel.Row).Interior.ColorIndex = 8 
                    case 21 to 40
                       Range("K" & cel.Row).Interior.ColorIndex = 9
                    ' and so on 
                    case Else 
                       Range("K" & cel.Row).Interior.ColorIndex = xlColorIndexNone 
                End Select 
            Case "Grangemouth" 
                If Range("K" & cel.Row).Value < 50 Then 
                    Range("K" & cel.Row).Interior.ColorIndex = 6 
                Else 
                    Range("K" & cel.Row).Interior.ColorIndex = xlColorIndexNone 
                End If 
        End Select 
    End If 
Next cel 
End Sub

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Download your Guide
GrahamSkanRetiredCommented:
You could do it like this:
            Case "Stanlow"
                Select Case Range("K" & cel.Row).Value
                    Case Is > 50
                        Range("K" & cel.Row).Interior.Color = vbRed
                    Case Is > 39
                        'Range("K" & cel.Row).Interior.Color = (Unspecified(
                    Case Is > 20
                        Range("K" & cel.Row).Interior.Color = vbGreen
                    Case Is > 1
                        Range("K" & cel.Row).Interior.Color = vbBlue
                End Select

Open in new window

0
RunriggerCommented:
Surely as mentors, we should be giving guidance where possible allowing the author to perform at least some kind of attempt at remediating his own code, allowing him to learn!

I don't really advocate writing it verbatim for them unless they have absolutely no knowledge, this author clearly has some.
0
Rory ArchibaldCommented:
That depends on your point of view. Many posters don't actually seem to want anything other than the answer; others will ask follow-up questions if they don't understand.
I personally don't see myself as anyone's mentor - I just try to help.
And FWIW, I don't see a lot of difference between what you did and what I did - I just entered it in the relevant location in the existing code. :)
0
DarrenJacksonAuthor Commented:
thank you all for the asistance the explanations from runrigger and the enlightenment in the use of ranges in case statements from rarya has given me enough knowledge on how to fix this.

As always thank you all
0
DarrenJacksonAuthor Commented:
Thankyou
0
RunriggerCommented:
rory, its a shame that I can't send you a personal message, but here goes anyway.

I have been observing your input for some time now and I personally have been learning tons of new stuff as a result.

I want to get better at this, ee introduced the "follow" to allow other experts to be mentored by following the better more experienced coders out there, I see you and all of the others experts as mentors for me in addition to authors.

Whenever a solution is pretty much given in its entirety, I feel less likely to have learned something!
0
Rory ArchibaldCommented:
Dave,
You make a good point, but bear in mind that a lot of the time I am making quick posts in between other tasks that I am doing, and it's often easier for me to just fix something than to explain the principle behind it (though I am always happy if someone wants to follow up for an explanation). Other times, I will go into long and unwarranted explanations of things that the OP couldn't give a monkey's about - just depends on my mood. :)
I've just noticed that there are now 64 people following me (nice to have a stalker clan), so I guess I should make more of an effort...
Rory

PS Yes, it would be nice if we had a PM system like almost every other Excel forum in the world! And automatic signatures. And...
0
RunriggerCommented:
Popular guy Rory, keep it rocking, I never intend for things to get personal and it certainly is not the case this time around either.

Give me the address of another forum and I will turn up on that one too, there are probably many, let me know the better ones please.

Cheers
Dave
0
Rory ArchibaldCommented:
My usual ones are MrExcel.com, ExcelForum.com, thecodecage.com, eileenslounge.com and VBAExpress.com from time to time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.