Select case

[John Davis]

Hello All:

Just checking is it proper to nest Select Case's as in the following?

Sub StartingOut()


If Range("N3").Value = "ball" Then

    Select Case Range("B3").Value
    
    Case Is = "P1"

        Select Case Range("H3").Value
        
        Case Is <= #2:00:00 AM#
        
                Range("H3").Interior.ColorIndex = 4
                
        Case Is > #2:00:00 AM#
        
                Range("H3").Interior.ColorIndex = 3
                
        End Select
        
    Case Is = "P2"
    
        Select Case Range("H3").Value
        
        Case Is <= #6:00:00 AM#
        
                Range("H3").Interior.ColorIndex = 4
                
        Case Is > #6:00:00 AM#
        
                Range("H3").Interior.ColorIndex = 3
                
        End Select

        
End If

End Sub

Or is there a better way to do this?

 

[p45cal]

It is.

Just checking is it proper to nest Select Case's as in the following?

 

[Andrew Fergus]

Whilst it may be correct technically, I'm curious why you would use Select Case in place of an If statement where there are only two choices? Also, check out 'Case Else' - if something is not <=2am, then it must be the alternative.

Andrew

 

[John Davis]

Whilst it may be correct technically, I'm curious why you would use Select Case in place of an If statement where there are only two choices? Also, check out 'Case Else' - if something is not <=2am, then it must be the alternative.

Andrew

Thanks for the replies and insight. Actually I started out trying too put everything in If statements, but for me I found it easier to construct the Select Cases. To be honest, I never even thought about 'Case Else'. It always best too have another set of eyes. Thanks.

 

[p45cal]

Conditional formatting can also do this, with the advantages:
1. that it will remove colour if not needed (though to make the code do it would be easy)
2. it updates instantly (if that's what you want), though event code could do this too.

Excel Workbook
	H
3	03:00:00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3	1. / Formula is =IF(AND($N3="ball",OR($B3="P2",$B3="P1")),$H3>IF($B3="P1",TIME(2,0,0),IF($B3="P2",TIME(6,0,0))))	Abc
H3	2. / Formula is =IF(AND($N3="ball",OR($B3="P2",$B3="P1")),$H3<=IF($B3="P1",TIME(2,0,0),IF($B3="P2",TIME(6,0,0))))	Abc

Just make sure there isn't any left over formatting in the cell from running the code as this will show if N3 isn't 'ball' or B3 is neither 'P1' or 'P2' (ie when there's no conditional formatting applying)!

 

[John Davis]

Conditional formatting can also do this, with the advantages:
1. that it will remove colour if not needed (though to make the code do it would be easy)
2. it updates instantly (if that's what you want), though event code could do this too.

Excel Workbook
	H
3	03:00:00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3	1. / Formula is =IF(AND($N3="ball",OR($B3="P2",$B3="P1")),$H3>IF($B3="P1",TIME(2,0,0),IF($B3="P2",TIME(6,0,0))))	Abc
H3	2. / Formula is =IF(AND($N3="ball",OR($B3="P2",$B3="P1")),$H3<=IF($B3="P1",TIME(2,0,0),IF($B3="P2",TIME(6,0,0))))	Abc

Thanks for pointing that out. I probably should use conditional formating more often, but I guess I'm just a creature of habit.