We help IT Professionals succeed at work.
Get Started

Modify existing code to allow for multiple case statements

187 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

Comment
Watch Question
This problem has been solved!
Unlock 3 Answers and 12 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE