Finding all cells that have been filled with any color and highlighting corresponding column headers in excel vba
<p><strong>My problem:</strong></p>

<p>I've made a large (2,000 line) macro that runs on our company's template and fixes some common issues  and highlights other issues we have prior to importing. The template file always has 150 columns and is in most instances 15,000+ rows (sometimes even over 30,000). The macro works well, highlighting all the cells that contain errors according to our data rules, but with a file with so many columns and rows I thought it'd be convenient to add a snippet to my macro that would have it find all of the cells that have been highlighted and then highlight the column headers of the columns that contain those highlighted cells. </p>

<p><strong>Methods I've found while searching for a solution:</strong></p>

<ul>
<li><p><a href="https://msdn.microsoft.com/en-us/library/office/ff196157.aspx" rel="nofollow">SpecialCells</a><code>xlCellTypeAllFormatConditions</code> only works for conditional formatting, so that isn't a plausible method for my situation</p></li>
<li><p>Rick Rothstein's UDF from <a href="http://answers.microsoft.com/en-us/office/forum/office_2003-customize/vba-code-to-identify-if-a-row-has-any-colored/cbc59a54-4b6b-43e4-9218-ae5db59255aa?auth=1" rel="nofollow">here</a></p>

<pre><code>Sub FindYellowCells()
  Dim YellowCell As Range, FirstAddress As String
  Const IndicatorColumn As String = "AK"
  Columns(IndicatorColumn).ClearContents
  '   The next code line sets the search for Yellow color... the next line after it (commented out) searches
  '   for the ColorIndex 6 (which is usually yellow), so use whichever code line is applicable to your situation
  Application.FindFormat.Interior.Color = vbYellow
  'Application.FindFormat.Interior.ColorIndex = 6
  Set YellowCell = Cells.Find("*", After:=Cells(Rows.Count, Columns.Count), SearchFormat:=True)
  If Not YellowCell Is Nothing Then
    FirstAddress = YellowCell.Address
    Do
      Cells(YellowCell.Row, IndicatorColumn).Value = "X"
      Set YellowCell = Cells.Find("*", After:=YellowCell, SearchFormat:=True)
      If YellowCell Is Nothing Then Exit Do
    Loop While FirstAddress &lt;&gt; YellowCell.Address
  End If
End Sub
</code></pre>

<p>This would be perfect with a few tweaks, except our files can have multiple colorfills. Since our template is so large I've learned that it takes quite some time to run one instance of <code>Find</code> to find just one colorfill in the <code>UsedRange</code>.</p></li>
<li><p>Using filtering, maybe cycling through all the columns and checking each if they contain any cell that has any colorfill. Would that be any faster though?</p></li>
</ul>

<p><strong>So, my question:</strong></p>

<ol>
<li>How could I accomplish finding all columns that contain <em>any</em> colorfilled cells? More specifically, what would be the most efficient (fastest) way to achieve this?</li>
</ol>