Select case - - too easy?

[lostmule]

Hello all, I hope all is good!

Below is a very short version of a very long 'select case' I have in one of my sheets.

-- start here --

Dim blah As Integer
Dim DataRange As Variant
Dim IcolX As Integer
Dim FcolX As Integer
Dim IpatX As Integer
Dim rnArea As Range
Dim rnCell As Range


blah = Cells(2, 9) 'Number for my last row
Set rnArea = Range("I6:AM" & blah)

For Each rnCell In rnArea

With rnCell
Select Case .Value

Case "LA", "LB", "LC", "LD"
'do this

Case "BA"
'do that

Case "CA", "CB"
'This is another

End Select

End With
Next

-- end here --

My question is this: I have another sheet (sheet2) who have 3 list: A1:A5 (LA, LB, LC, LD and LE) respectively. B1:B5 (BA, BB [...] BE) and C1:C5 (CA, CB [...] CE).

Would there be a way of using 'case "LA", "LB", "LC" link to sheet two cells A1:A5? I just find my current way to long and complex... thought??

Thanks!

E.

 

[tweedle]

There may very well be an easier way, but I couldn't get the worksheetfunctions to work
So wrote my own dangit.
might drop the Select Case altogether and go to IF statements as such...

With rnCell
    If ISIN(.Value, Sheets(2).Range("A1:A5")) Then
         .Font.Bold = Not .Font.Bold
    End If
End With
Next

Function ISIN(testval As Variant, testrange As Range) As Boolean
ISIN = False
Dim C As Range
For Each C In testrange
    If C = testval Then ISIN = True
Next C
End Function

performance disclaimer: this is not tested on large data sets

 

[tweedle]

This is using the WorksheetFunction with IFs....

For Each rnCell In rnArea
With rnCell
    If InStr(1, WorksheetFunction.CountIf(Sheets(2).Range("A1:A5"), "=" & .Value)) Then
         .Font.Bold = Not .Font.Bold
         .Interior.ColorIndex = 7
    End If
End With
Next

note: the calls to WorksheetFunction are sometimes not the most efficient

 

[lostmule]

I walked away from 'IF' because I found it was slowing down the process.. but I think what you are proposing is likely the only course of action I can consider... I will expand on your code and see where it leads me

Eric