Select Case

[rama4672]

I have the following bit of code, what i want it to do is colour the cell according to the value, at the moment it will do the first one which is HOL, but not the rest of them, do i need to set it up as a select case, if so how do i do that.

Range("d:d").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""hol"""
Selection.FormatConditions(1).Interior.ColorIndex = 39
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""sick"""
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""r"""
Selection.FormatConditions(1).Interior.ColorIndex = 45

Alao when that has been completed i want to look for certain cell values, when it finds them i want to insert an entire row.
can that be done using the same select case.



Thanks For any help

Ian

[shades]

Something like this, you would have to adjust some, and define myRng, but this should get you started.

Select Case myRng
                 Case "hol":
                     .Interior.ColorIndex = 39
                 Case "sick"
                     .Interior.ColorIndex = 4
                 Case "r":
                     .Interior.ColorIndex = 45
                 Case Else
                     .Interior.ColorIndex = 1
             End Select

[Ken Puls]

Hi there,

To run on all cells in column D with data in them:

Sub test()
 Dim cl As Range
 For Each cl In ActiveSheet.Range("D1:D" & ActiveSheet.Range("D65536").End(xlUp).Row)
     Select Case cl.Value
         Case Is = "hol"
             cl.Interior.ColorIndex = 39
         Case Is = "sick"
             cl.Interior.ColorIndex = 4
         Case Is = "r"
             cl.Interior.ColorIndex = 45
         Case Else
             cl.Interior.ColorIndex = 1
     End Select
 Next cl
 End Sub

You could also nest an If statement or another case either within the individual cases or before the Next Cl statement to insert a row. If you need help, just let us know the criteria.

Also, you may want to consider either adding an Option Compare Text just after the Option Explicit statement or converting both the case to be tested (cl) to lowercase to avoid case sensitivity issues. (If a user enters Hol instead of hol, for example.)

HTH,

[Paleo]

Hi Iam,

to insert the row as you have asked you may use this:

Sub test() 
   Dim cl As Range 
   For Each cl In ActiveSheet.Range("D1:D" & ActiveSheet.Range("D65536").End(xlUp).Row) 
      Select Case cl.Value 
         Case Is = "hol" 
            cl.Interior.ColorIndex = 39
         Case Is = "sick" 
            cl.Interior.ColorIndex = 4 
         Case Is = "r" 
            cl.Interior.ColorIndex = 45 
         Case Else 
            cl.Interior.ColorIndex = 1 
      End Select
      'If you want to insert a row in all use this, else put it on where you want to insert a row
      cl.Select
      Selection.EntireRow.Insert
   Next cl 
End Sub

[Zack Barresse]

Hi,

Paleo, there are some things you don't need to put, in lieu of performance/assumptions. You don't need to put the ActiveSheet, it's implied. And you don't need to select anything, you would already be iterating through the range object.

[Paleo]

Ok, thanks. I will do this in my next posts.

[rama4672]

Thanks for all the replys, I have just finished a 13 hour night shift and i am now going to bed, so when i get up i will try some of the suggestions

Ian

[rama4672]

Ok I have now tried the codes supplied but what i get is a black interior color in col d:d


Thanks


Ian

[Jacob Hilderbrand]

This line makes all the cells that do not match one of the criteria black.

Case Else
cl.Interior.ColorIndex = 1

To leave them uncolored try this.

Case Else
cl.Interior.ColorIndex = xlNone

[rama4672]

Thanks for that Jake, that now works.

Just one last thing though, How do i get it to not be case sensitive, as sometimes they could be all in caps or lower case, as it only picks out the lower case at the moment.

Thanks

Ian

[Richie(UK)]

Hi Ian,

You could use the LCase function to convert the text being examined in the Select Case construct into all lowercase and then compare it to lowercase text. Alternatively, use the UCase function to compare it to uppercase text.

Like this:

Sub test()
If LCase(Sheet1.Range("A1").Value) = "ian" Then
        MsgBox "Its Ian!"
    End If
    '
    'or
    '
    If UCase(Sheet1.Range("A1").Value) = "IAN" Then
        MsgBox "Its Ian!"
    End If
End Sub

HTH

[Zack Barresse]

Edit: Didn't see Richie's post!! Oops! I guess I'll leave this up; maybe it'll help somebody.


Use either UCase or LCase. If you kept all of your text lower case in your individual Case statements, use LCase. An example of each (w/ the code above) ...

Using LCase:

Sub test() 
    Dim cl As Range 
    For Each cl In ActiveSheet.Range("D1:D" & ActiveSheet.Range("D65536").End(xlUp).Row) 
        Select Case LCase(cl.Value) '<<-- This is where you set the case
        Case Is = "hol" 
            cl.Interior.ColorIndex = 39 
        Case Is = "sick" 
            cl.Interior.ColorIndex = 4 
        Case Is = "r"
            cl.Interior.ColorIndex = 45 
        Case Else 
            cl.Interior.ColorIndex = 1 
        End Select 
    Next cl 
End Sub

Using UCase:

Sub test() 
    Dim cl As Range 
    For Each cl In ActiveSheet.Range("D1:D" & ActiveSheet.Range("D65536").End(xlUp).Row) 
        Select Case UCase(cl.Value) '<<-- This is where you set the case
        Case Is = "HOL" 
            cl.Interior.ColorIndex = 39 
        Case Is = "SICK" 
            cl.Interior.ColorIndex = 4 
        Case Is = "R"
            cl.Interior.ColorIndex = 45 
        Case Else 
            cl.Interior.ColorIndex = 1 
        End Select 
    Next cl 
End Sub

So basically, just force the capitalization of the value being checked, as it's basically a variable, and make your Case statements match.


HTH

[mdmackillop]

To ignore case, use Option Compare Text at the start of your module.
MD

[rama4672]

Thank you for everybodys help here.
Md that was what i was looking for, so if some one made the mistake of putting lowercase instead of upper it didn't matter.
It now works a treat, apart from it now takes about 40 secs to run the macro where as i used to take 6-7 secs, is there anyway that would speed this up a bit.

Thanks

Ian

[Jacob Hilderbrand]

We can speed up the loop by limiting the iterations. Since we only want to change cells with text we can use Special Cells to narrow the range. If you have a lot of cells in the range that are blank, this should help a lot.

Option Compare Text
 Option Explicit
 
 Sub test()
Dim cl              As Range
 Dim Rng1            As Range
Set Rng1 = Range("D1:D" & Range("D65536").End(xlUp).Row)
     Rng1.Interior.ColorIndex = xlNone
     Set Rng1 = Rng1.SpecialCells(xlCellTypeConstants, 2)
     For Each cl In Rng1
         Select Case cl.Text
         Case Is = "HOL"
             cl.Interior.ColorIndex = 39
         Case Is = "SICK"
             cl.Interior.ColorIndex = 4
         Case Is = "R"
             cl.Interior.ColorIndex = 45
         End Select
     Next cl
End Sub

[rama4672]

Thank you for that DRJ.

It is now working perfect, infact it is faster than it was before, it now takes about 3-4 secs.

Thank you to all who helped with this.
I think i can now safely say that this thread is Solved.





Ian