?
Solved

Modify existing code to allow for multiple case statements

Posted on 2010-03-26
12
Medium Priority
?
172 Views
Last Modified: 2012-05-09
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

0
Comment
Question by:DarrenJackson
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 11

Accepted Solution

by:
Runrigger earned 800 total points
ID: 28681959
select case "value"
case "Stanlow"

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

Expert Comment

by:Runrigger
ID: 28682255
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 800 total points
ID: 28682617
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 77

Assisted Solution

by:GrahamSkan
GrahamSkan earned 400 total points
ID: 28682910
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
 
LVL 11

Expert Comment

by:Runrigger
ID: 28683180
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 28683551
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
 

Author Comment

by:DarrenJackson
ID: 28684358
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
 

Author Closing Comment

by:DarrenJackson
ID: 31707512
Thankyou
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 28684541
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 28685533
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
 
LVL 11

Expert Comment

by:Runrigger
ID: 28686562
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 28687097
My usual ones are MrExcel.com, ExcelForum.com, thecodecage.com, eileenslounge.com and VBAExpress.com from time to time.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question