tidy betty

[fry]

Hi again all

Could someone please help me tidy up and condense the following code...

For r = 11 To 12
On Error Resume Next
w = WorksheetFunction.Search("boc", Cells(r, "B"))
x = WorksheetFunction.Search("a-plant", Cells(r, "B"))
y = WorksheetFunction.Search("a plant", Cells(r, "B"))
z = WorksheetFunction.Search("adlington", Cells(r, "B"))
If w > 0 Then
ActiveSheet.Tab.ColorIndex = 5
End If
If x > 0 Then
ActiveSheet.Tab.ColorIndex = 10
End If
If y > 0 Then
ActiveSheet.Tab.ColorIndex = 10
End If
If z > 0 Then
ActiveSheet.Tab.ColorIndex = 10
End If
Next

Many Thanks

P.S. Sorry for the DOUBLE post above but my puter crashed mid submit!!

 

[ravishankar]

Hi
try the following codes

Sub hhh()
For a = 1 To 4
b = Choose(a, "boc", "a-plant", "a plant", "adlington")
c = Choose(a, 5, 10, 10, 10)
If Cells(11, 2) = b Or Cells(12, 2) = b Then
ActiveSheet.tab.ColorIndex = c
End If
Next a
End Sub

Ravi

 

[facethegod]

Option Base 1
Public Sub trythis()
On Error Resume Next
Dim arr() As Variant
Dim i As Integer
arr = Array("boc", "a-plant", "a plant", "adlington")
For i = 1 To 4
        If Not IsError(Application.Search(arr(i), Cells(11, "B"))) Or Not IsError(Application.Search(arr(i), Cells(12, "B"))) Then
            Select Case i
                Case 1
                ActiveSheet.Tab.ColorIndex = 5
                Case Is > 1
                ActiveSheet.Tab.ColorIndex = 10
            End Select
        End If
Next
End Sub

 

[Norie]

This is a shot in the dark since I've no idea if there could be any other values in B11/12 that you need to deal with.

    If InStr(Range("B11"), "boc") <> 0 Or InStr(Range("B12"), "boc") <> 0 Then
        ActiveSheet.Tab.ColorIndex = 5
    End If
    
    If Left(Range("B11"), 1) = "a" Or Left(Range("B12"), 1) = "a" Then
        ActiveSheet.Tab.ColorIndex = 10
    End If