troubleshooting Question

Modify existing code to allow for multiple case statements

Avatar of DarrenJackson
DarrenJacksonFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelMicrosoft Office
12 Comments3 Solutions188 ViewsLast Modified:
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

ASKER CERTIFIED SOLUTION
Runrigger

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros