title: VBA - Looping through all ListObjects across a range of worksheets
body: <p>I have a table conditional formatting macro (thanks Jeeped) that I would like to expand to loop through all tables across a range of worksheets. I suspect this is not the most efficient approach but the its the best I could cobble together, and even then it's not working. So far I'm stuck at two points below. Any assistance is greatly appreciated!</p>
  
  <p>1) Setting ws to equal multiple worksheet codenames (e.g. <code>Set ws = Worksheets(5,6,7)</code>)</p>
  
  <p>2) Setting the range w/o a run-time error <code>Set myRange = ws.ListObjects.DataBodyRange</code> produces "Run-time error '438': Object doesn't support this property or method"</p>
  
  <p>Current code is:</p>
  
  <pre><code>Sub ConditionalFormatting()
      Dim ws As Excel.Worksheet
      Dim lo As Excel.ListObject
      Dim myRange As Range
  
      Set ws = Worksheet(5) 'Would like to expand to include multiple worksheets!
      Set myRange = ws.ListObjects.DataBodyRange
  
      For Each lo In ws.ListObjects
          With lo.FormatConditions
              .FormatConditions.Delete
              Call FormatRange(myRange, 10, "$E5=INDEX(Location,1,1)") 'Warehouse1
              Call FormatRange(myRange, 10, "$E5=INDEX(Location,2,1)") 'Warehouse2
              Call FormatRange(myRange, 10, "$E5=INDEX(Location,3,1)") 'Warehouse3
          End With
      Next lo
  End Sub
  
  Public Sub FormatRange(r As Range, clr As Integer, frml As String)
      r.FormatConditions.Add Type:=xlExpression, Formula1:=frml
      r.FormatConditions(r.FormatConditions.Count).Font.colorindex = clr
  
      With r.FormatConditions(1).Borders(xlTop)
          .LineStyle = xlContinuous
          .Color = color
          .TintAndShade = 0
          .Weight = xlThin
      End With
      With r.FormatConditions(1).Borders(xlBottom)
          .LineStyle = xlContinuous
          .Color = color
          .TintAndShade = 0
          .Weight = xlThin
      End With
      r.FormatConditions(1).StopIfTrue = False
  End Sub
  </code></pre>