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.
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!
One common type of workbook used in offices is one that contains a single worksheet for each month of the year. If you ...
Discover MoreMacros are very powerful, but you may not want them to always be available to a user. Here are some ways you can limit ...
Discover MoreThe personal macro workbook is a great place to save your macros that you want to use with any workbook. The personal ...
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 © 2024 Sharon Parq Associates, Inc.
Comments