PDA

View Full Version : Solved: Select Case



austenr
01-30-2006, 11:55 AM
Hi everyone,

I need to use select case to color entire rows based on a value in column O.

If someone can get me started I can finish. Thanks.

mdmackillop
01-30-2006, 12:09 PM
Sub Colours()
Dim cel As Range
For Each cel In Intersect(Columns(15), ActiveSheet.UsedRange)
Select Case cel
Case Is = 1
cel.EntireRow.Interior.ColorIndex = 6
End Select
Next
End Sub

Shazam
01-30-2006, 12:15 PM
Here is another example.


Sub Color()

Dim c As Range
For Each c In Range("O:O")
Select Case c
Case 12
c.EntireRow.Interior.ColorIndex = 42
Case 15
c.EntireRow.Interior.ColorIndex = 35
Case 6
c.EntireRow.Interior.ColorIndex = 9
End Select
Next c
End Sub

austenr
01-30-2006, 01:17 PM
:cloud9: Thanks Md and Shazam. That helped a lot. Marking it solved.

mdmackillop
01-30-2006, 03:33 PM
Hi Shazam,
Your code requires the checking of all 65536 cells in column O, if you have multiple columns to check, eg A:O, you'll find that your code is noticably slower. Add in a timer to compare codes. eg

Sub Color()
Dim c As Range
Dim tim As Double

tim = Timer
For Each cel In Intersect(Range("A:O"), ActiveSheet.UsedRange)
Select Case cel
Case Is = 1
cel.EntireRow.Interior.ColorIndex = 6
End Select
Next
[a1] = Timer - tim
[b1] = "Restricted"


tim = Timer
For Each c In Range("A:O")
Select Case c
Case 12
c.EntireRow.Interior.ColorIndex = 42
End Select
Next c
[a2] = Timer - tim
[b2] = "open"

End Sub