Using Find and Replace to Find Conditionally Formatted Cells

Written by Allen Wyatt (last updated February 13, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


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 Microsoft 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

Extracting Targeted Records from a List

When working with large amounts of data, you may have a need to extract just the information that meets the criteria you ...

Discover More

Understanding ZIP Folders

Need to move a lot of information to someone else? The answer may be to store that information in a ZIP folder. Here's ...

Discover More

Handling Negative Numbers in a Complex Custom Format

Custom formats are great for defining how a specific value in a cell should look. They aren't that great at doing complex ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Getting a List of Matching Cells

The Find and Replace capabilities of Excel allow you to easily locate all the cells in a worksheet that contain specific ...

Discover More

Changing the Color Used to Highlight Found Information

When you want to find information in worksheet, Excel can handily locate and highlight that information. If you find the ...

Discover More

Finding and Replacing with Subscripts

Want to use Find and Replace to change the formatting of a cell's contents? You would be out of luck; Excel won't let you ...

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}] (all 7 characters, in the sequence shown) 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 seven 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.