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

Form Fields in Headers and Footers

When working with form fields, you may want to place those fields in the header or footer of a document. Word won't let ...

Discover More

Inserting Rows

Need to insert rows in your worksheet? Excel provides a few techniques you can use to do this. Here are some ideas you ...

Discover More

Generating Automatic Links to Audio Files

Need to create links to many filenames on your system? Here are a couple of quick ways to get just the connection you need.

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Limiting Searching to a Column

When you use Find and Replace, Excel normally looks through all the cells in a worksheet. You may want to limit the ...

Discover More

Searching for Leading Apostrophes

Take a look at the Formula bar when you select a cell that contains text, and you may see an apostrophe at the beginning ...

Discover More

Searching for Wildcards

Wildcard characters can be used within the Find and Replace tool, but what if you want to actually search for those ...

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 7 + 4?

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.