How would one code a case statement in Excel VBA to do the following?

what code do you have now?

try this http://www.ozgrid.com/Excel/free-training/ExcelVBA1/excelvba1lesson13.htm

Is your trouble figuring out what information to case select on? Or the actual syntax of a select case. Or are you trying to write a complicated one.

A common confusion is trying to use a case statement where an elseif would be better. But I find case statements easier to read. So to make a case statement behave like an elseif. Do this.

[pre]
Select Case True 'This will make VBA stop on the first true case statement
case cell(1,2)="Test sheet 1"
case cell(1,2)="Test sheet 2" and cell (3,4) = "Important test"
case msgbox("Wasn't first case, should we keep trying?",[params]) = vbno 'If user picks no, case stops cause it evaluates to true
case FunctionName 'this is a function that returns true or false, a return of true ends the case statement
End Select
[/pre]

The use of this method over an elseif becomes clear when you have 50 or more possibilities.

That is just some of the neat... abusive ways to use a case statement in VBA.