Clearing all tab color before highlighting particular tabs based on range values

[OliviaT]

I have a workbook with about 150 to 200 tabs (changes each month). Searching for a particular tab can be quite time consuming. I modified a vba code from another workbook to help highlight the tab(s) that I am looking for based on the name I entered in a range of cells. The code works for highlighting the tab. However, if I do a second or third round of searches, the original highlighted tabs from the first search are still there. I would like to modified the vba code to clear all tabs of all color before highlighting specific tab based on my new search. Could someone please help me with the vba code below. I bold the line I am having issues with in the code. I am new to vba. Thank you in advance for your time and assistance.


Sub Highlight_Tab()

Dim ws As Worksheet
Dim rng As Range

ws.Tab.ColorIndex = xlColorIndexNone


For Each rng In Sheets("Summary").Range("O35:O38")
For Each ws In ThisWorkbook.Sheets
If ws.Name = rng.Value Then
ws.Tab.ColorIndex = 4


End If

Next ws
Next rng



End Sub

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG03Jun49
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ws [COLOR="Navy"]In[/COLOR] ThisWorkbook.Sheets
         ws.Tab.ColorIndex = xlColorIndexNone
     [COLOR="Navy"]Next[/COLOR] ws
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] rng [COLOR="Navy"]In[/COLOR] Sheets("Summary").Range("O35:O38")
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ws [COLOR="Navy"]In[/COLOR] ThisWorkbook.Sheets
        [COLOR="Navy"]If[/COLOR] ws.Name = rng.Value [COLOR="Navy"]Then[/COLOR] ws.Tab.ColorIndex = 4
    [COLOR="Navy"]Next[/COLOR] ws
[COLOR="Navy"]Next[/COLOR] rng
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[OliviaT]

Thank you, MickG!!! It worked. I knew I was close but didn't think to add a "For" and "Next" in the loop. Thanks again...very much appreciated!!!

 

[MickG]

You're welcome