Written by Allen Wyatt (last updated January 26, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Cindy has a fully formatted worksheet that uses color in many cells. Some of the cells have values in them; many do not. She needs a way to count any colored cells that are empty and wonders if there is a quick way to do this.
There are a few ways you can get the information you need. One way is to go through these steps:
Figure 1. The Go To Special dialog box.
Figure 2. The Find tab of the Find and Replace dialog box.
Figure 3. The Fill tab of the Find Format dialog box.
When you perform these steps, Excel shows, at the bottom of the Find and Replace dialog box, how many cells it found that match your color. Since you started the search with only blank cells selected, the resulting count is all those cells that are blank that are filled with the color.
Of course, if you need to determine this count quite a few times, then these steps can get very tedious very quickly. In such cases it is a better idea to use a macro. The following macro steps through each blank cell in whatever range you have selected and checks to see if it contains a pattern or a color and is empty. If the conditions are fulfilled, then a counter for that color is incremented.
Sub CountBlankColors1() Dim c As Range Dim J As Integer Dim ColorCount(56) As Long ActiveSheet.Range("a1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Select For Each c In Selection With c.Interior If .Pattern <> xlNone Then If .ColorIndex <> xlNone Then If IsEmpty(c) Then ColorCount(.ColorIndex) = _ ColorCount(.ColorIndex) + 1 End If End If End If End With Next c sTemp = "These are the color counts" & vbCrLf & vbCrLf For J = 0 To 56 If ColorCount(J) > 0 Then sTemp = sTemp & "Color " & J & ": " & ColorCount(J) & vbCrLf End If Next J MsgBox sTemp End Sub
Of course, you might not want to count different colors individually. Instead, you might want to know simply how many blank cells are filled with any color, in aggregate. In that case the macro becomes much simpler.
Sub CountBlankColors2() Dim c As Range Dim x As Long x = 0 ActiveSheet.Range("a1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Select For Each c In Selection If c.Interior.Pattern <> xlNone Then If c.Interior.ColorIndex <> xlNone Then If IsEmpty(c) Then x = x + 1 End If End If Next c MsgBox "Number of colored blank cells: " & x End Sub
It should be noted that these approaches don't take into consideration if the cell is colored through the use of a conditional format or not. (In fact, they don't take conditional formats into account at all.)
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12581) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
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!
Grab some info from a source other than Excel, and you may find the need to delete a certain pattern of rows from a ...
Discover MoreISBNs are used to uniquely identify books. You may need to know if an ISBN is valid or not, which involves calculating ...
Discover MoreWhen running a macro, have you ever seen Excel appear to stop responding? This can be frustrating, but there are a couple ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments