PDA

View Full Version : [SOLVED] Select Case



rama4672
01-27-2005, 08:56 AM
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
01-27-2005, 09:31 AM
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
01-27-2005, 09:40 AM
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
01-27-2005, 11:35 AM
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
01-27-2005, 11:50 AM
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
01-27-2005, 07:15 PM
Ok, thanks. I will do this in my next posts.

rama4672
01-28-2005, 12:05 AM
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
01-30-2005, 03:35 AM
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
01-30-2005, 04:33 AM
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
01-30-2005, 08:46 AM
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)
01-30-2005, 09:35 AM
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
01-30-2005, 10:29 AM
Edit: Didn't see Richie's post!! Oops! I guess I'll leave this up; maybe it'll help somebody. :dunno


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
01-30-2005, 01:01 PM
To ignore case, use Option Compare Text at the start of your module.
MD

rama4672
02-01-2005, 12:05 AM
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
02-01-2005, 12:30 AM
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
02-01-2005, 08:02 AM
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.


:bigdance2


Ian