Using Find and Replace to Find Conditionally Formatted Cells

Richard is trying to use Find and Replace to count cells formatted using a particular fill color. This works fine, except when the fill is the result of Conditional Formatting. In this case Find and Replace doesn't find them. However, it's still possible to filter rows containing conditionally formatted cells based on their fill color. Richard wonders why, if Autofilter is able to detect either type of cell formatting, Find and Replace can't locate those cells.

Anything that might be offered as a "why" would, of course, be speculation. (There are many times I've wondered why Microsoft chose to do things they way they did.) That being said, it makes sense that Find and Replace would be coded so that you can find things that can be replaced. Fill colors displayed as a result of Conditional Formatting are just that—a display color, not a real fill color. Display colors cannot be replaced, so it follows they cannot be found.

You can, however, use a different method to display the count you want—a macro. The following macro looks at all the cells in a particular range and if a match to a desired color is found, then the counter is incremented. (It is the .DisplayFormat object that is examined, so it the color "as displayed," which means that it also matches what Conditional Formatting may show.)

Sub CountCellColors()
    Dim Rng As Range
    Dim c As Range
    Dim Colr As Variant
    Dim J As Integer
    Dim sTemp As String

    Set Rng = Range("A1:Z500") 'Change as needed
    Colr = vbYellow            'Set color you want to count

    J = 0
    sTemp = ""
    For Each c In Rng
        If c.DisplayFormat.Interior.Color = Colr Then
            J = J + 1
            sTemp = sTemp & vbCr & "     " & c.Address
        End If
    Next c

    Select Case J
        Case 0
            sTemp = "There are no colored cells in the range."
        Case 1
            sTemp = "There is 1 colored cell in the range:" _
              & vbCr & sTemp
        Case Else
            sTemp = "There are " & J & " colored cells in the range:" _
              & vbCr & sTemp
    End Select
    MsgBox sTemp
End Sub

When the macro is done running, it displays a message box that shows the number of color matches as well as the addresses of the cells that were matched. As written, the macro checks cells A1:Z500 and it looks for yellow fill in the cells. Both of the lines where these values are set can be changed to whatever is appropriate for your needs.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13742) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.