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

Returning the Smallest Non-Zero Value

In a series of values, you may need to know the smallest value that isn't a zero. There is no built-in function to do ...

Discover More

Plotting Times of Day

Got a chart created from your worksheet? You can plot times of day in the chart if you apply the simple techniques in ...

Discover More

Disabling Shift+Ctrl

Both Excel and Windows have scores of defined shortcut keys. This can be great for productivity, but it can be a real ...

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)

Making All Occurrences Bold

Want to make instances of a given word or phrase bold throughout a worksheet? Here's a way you can make the change quickly.

Discover More

Limitations On Finding Characters

When you search for information in a worksheet, you expect Excel to return results that make sense. If you don't get a ...

Discover More

Counting the Results of a Formula Using Find and Replace

Need to get a count of a particular result from a formula? You can use Find and Replace (as described in this tip), but ...

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 eight more than 6?

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.