by Allen Wyatt
(last updated March 10, 2018)
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.)
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 Office 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Do you need to create a number of words or phrases where you only alter a few letters in each one? If the alterations ...Discover More
Macros depend on the use of variables to do their work. This tip examines how variables are declared in a macro, using ...Discover More
When you use SUM to determine the total of a range of values, Excel doesn't really pay attention to whether the values ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.