title: How to create a one-click macro to color-code tabs based on the contents of the sheet name?
body: <p>I have a macro to import several files into one sheet and give them names with legal characters. These files and filenames will vary from time to time depending on the needs of the project. I would like to color code like sheets by sheet name.</p>
  
  <p>So I need to loop through all sheets looking for particular phrases. Like all sheets containing the word "page" would be colored blue. So Page Title Duplicates and Page Titles Too Long would both be blue tabs. While pages containing the phrase H1 would all be red. So H1 Duplicates and H1 Too Long would both be red tabs.</p>
  
  <p>There are times a title would include both page and H1, so I need to make an exception for that and chose a unique color.</p>
  
  <p>I realize I need to loop through all sheets checking for their existence and giving a case as to what the name contains and what color I want to use, but I can't seem to find a one-click macro that isn't a private sub to accomplish this.</p>
  
  <pre><code>Sub TabColor()
  
  Sheets("canonicals missing").Tab.Color = vbBlack
  Sheets("canonicals nonindexable canonic").Tab.Color = vbBlack
  Sheets("h1 duplicate").Tab.Color = vbRed
  Sheets("h1 missing").Tab.Color = vbRed
  Sheets("h1 multiple").Tab.Color = vbRed
  Sheets("h1 over 70 characters").Tab.Color = vbRed
  Sheets("page titles duplicate").Tab.Color = vbBlue
  Sheets("page titles missing").Tab.Color = vbBlue
  Sheets("page titles over 65 characters").Tab.Color = vbBlue
  Sheets("page titles same as h1").Tab.Color = vbBlue
  
  End Sub
  </code></pre>
  
  <p>I chose the exact title route because there are times they contain two keywords like "page" and "h1", so I figured it's better to be explicit. However, if those titles don't exist then it stops. The titles will always vary so there's no way to ensure exactly what the titles will be in any given batch of .xls files. I have no clue how to loop through each sheet and check for its existence. I'm very new to VBA and I'm stumbling.</p>