Using Find and Replace to Find Conditionally Formatted Cells

by Allen Wyatt
(last updated February 29, 2020)

3

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Hiding Rows Based on a Cell Value

This tip contains a macro to hide rows that contain data you don't want to see.

Discover More

Modified Rounding

Rounding is normally done so that values greater than or equal to .5 are rounded up and less than .5 are rounded down. ...

Discover More

Pop-Up Comments for Graphics

Excel allows you to add comments to individual cells in a worksheet, but what if you want to add comments to graphics? ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Find and Replace in Headers

Using Find and Replace is something quite routine in Excel, as it easily allows you to find and replace information in ...

Discover More

Replacing in Worksheets and Comments At the Same Time

If you need to replace information that may appear in cells, comments, and text boxes, your best bet is to use a macro. ...

Discover More

Finding and Replacing in Text Boxes

Finding and replacing information in a worksheet is easy. Finding and replacing in other objects (such as text boxes or ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 8?

2020-03-04 05:34:08

Patrick Verhaeghe

If there is a possibilty to filter you can Always perform a filter on the colored cels and the statusbar will show you the number of rows selected. Might help?
What I often do if counting is neccesary in a filtered list is putting a subtotal in a cel onder the particalar colom with the option 3 (number arguments" - AANTAL ARG in dutch). then the counting is dynamic on whatever you filter in any colom


2020-03-02 08:07:08

Allen

Use the RGB function:

Colr = RGB(255,153,0)

-Allen


2020-03-02 05:55:16

Richard Curtis

Allen, Thanks for publishing my question!
When I'm modifying the colour in the CountCellColors macro, how do I specify a RGB custom color? The particular combination I want is R 255, G 153, B 0.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.