Is there a simpler way of writing this control structure? 2

[qjd2004]

Hiya,

I've got a repetition structure with lots of double selection inside it. Is there a quicker way to write this (i.e. can I put a while within a for loop?) and what is the syntax?

For Each cl In r       
 	
   If cl.Value = "Client vs MAINFRAME DO NOT MATCH!" Then
	cl.Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Selection.Interior.ColorIndex = 6
   ElseIf cl.Value = "NO Client PRICE FOR THIS PRODUCT!" Then
        cl.Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Selection.Interior.ColorIndex = 6
   ElseIf cl.Value = "MISTMATCH!" Then
        cl.Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Selection.Interior.ColorIndex = 6
   ElseIf cl.Value = "MISMATCH: ACCMGR PENDING" Then
        cl.Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Selection.Interior.ColorIndex = 6
   ElseIf cl.Value = "MISSING FROM Client:  PLEASE ADVISE THE CLIENT!" Then
        cl.Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Selection.Interior.ColorIndex = 6
   ElseIf cl.Value = "NO RECORDS EXIST ON Client!" Then
        cl.Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Selection.Interior.ColorIndex = 6
   ElseIf cl.Value = "NO RECORDS ON MAINFRAME!" Then
        cl.Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Selection.Interior.ColorIndex = 6
   ElseIf cl.Value = "PLEASE VALIDATE WITH ACCMGR!" Then
        cl.Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Selection.Interior.ColorIndex = 6
   ElseIf cl.Value = "POSSIBLE MATCH: Client IS FACTOR OR MULTIPLE OF MFRAME" Then
        cl.Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Selection.Interior.ColorIndex = 34
   End If

Next cl

 

[IvanMoala]

For Each cl In r
   Select Case cl.Value
   Case "Client vs MAINFRAME DO NOT MATCH!", "NO Client PRICE FOR THIS PRODUCT!", _
        "MISTMATCH!", "MISMATCH: ACCMGR PENDING", "MISSING FROM Client:  PLEASE ADVISE THE CLIENT!", _
        "NO RECORDS EXIST ON Client!", "NO RECORDS ON MAINFRAME!", "PLEASE VALIDATE WITH ACCMGR!"
        Range(Range(cl.Address), Range(cl.Address).End(xlToLeft)).Interior.ColorIndex = 6
   Case "POSSIBLE MATCH: Client IS FACTOR OR MULTIPLE OF MFRAME"
        Range(Range(cl.Address), Range(cl.Address).End(xlToLeft)).Interior.ColorIndex = 34
   End Select
Next cl

Ivan F Moala

http://www.xcelfiles.com

 

[qjd2004]

Wonderful!

Thank you, that's much simpler and therefore easier to read.

I'll make use of Select Case again in future, I didn't even know there was a multiple selection structure in VBA.

Thanks!

 

[MakeItSo]

Good post.
;-)
Cheers,
MakeItSo