Technique to COUNTIF using background color?

[nomar116]

I am a USAF Major improving a self-designed scheduling tool used to track the schedule of 50-60 service members. I would like to develop an easier interface for COUNTIF a name appears within a non-contiguous range, most likely written in VBA.

The current method is done as a series of COUNTIF statements, depicted in the photo below. The user moves around the range boxes, or adds on another COUNTIF statement to expand the range selection, to capture whatever data needs to be counted. These boxes typically need to be moved and added/deleted weekly. The best part of this method is the user is given immediate feedback with the colored boxes so they know what ranges are being counted. The drawback is editing the cell, moving range boxes, and keeping track of everything manually, is cumbersome for an Excel novice or anytime there are lots of changes to keep track of. We sometimes even have to double count a range, and it can be difficult to tell when that double counting is working correctly.

I would like to make this easier for the user. Is there a VBA script I could write to COUNTIF where the range is dynamically selected based on cell background color? As an example, it would could anywhere the cell background was Blue and the name was present?

That would cover about half of the work I'm trying to do. I would still like to find an alternative, more user friendly method, to these COUNTIF statements such as a userform or inputbox. I would welcome any suggestions.

 

[Eric W]

I'm a little confused. Your example doesn't show ranges with the same background color. It is possible to create a UDF (User-Defined Function) that can COUNTIF based on value and color. If you want to try one, open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. In the sheet that opens, paste this code:

Public Function ColorCountIf(ByRef MyColor As Range, ByRef MyRange As Range, ByVal MyValue As String) As Long
    clr = MyColor.Interior.Color
    For Each c In MyRange
        If c.Interior.Color = clr And c.Value = MyValue Then ColorCountIf = ColorCountIf + 1
    Next c
End Function

Press Alt-Q to close the editor. Then set up your sheet similar to this:

Book1
	A	B	C	D	E	F	G	H	I
1	ed								Count
2			al						2
3
4	ed
5			ed
6			al
7
8	ed
9	ed
Sheet10

Cell Formulas
Range	Formula
I2	I2	=colorcountif(A1,A1:H20,"ed")

The first parameter in the function is a cell that has the color you want. The second parameter is the overall range that might contain cells with the color you want. The last parameter is the text value you want to find.

This should work for simple cases, but there are several issues with it. First, there are about 16 million color values. Two values that look the same to the naked eye might have different values, so they wouldn't be counted the same. Also, changing the background color of a cell does not trigger a recalculate, so if you do so, you may need to press F9 to get it to pick up the changes. There are several ways to adapt this, but let us know how/if this works for you.

 

[nomar116]

Eric,

Firstly, thank you for your reply, time and effort in solving my problem.

To your confusion and question: the screen shot I have provided has been significantly parsed down with lots of data removed. I did this for operational security purposes based on the private nature of this scheduling data. The actual document has far more names throughout the various highlighted and selected regions.

This macro is working great. I'm going to experiment with it and come back with a few more questions. One right off the bat is this, and my other macros, do not work when the range includse cells with a "value not available error" such as T35:T41. I have VLOOKUP functions that often return that error when the schedule is being "built."

Is there a way to get your suggested macro to overlook value not available?

 

[nomar116]

I found IFERROR(FUNCTION(),0) as an example of how I could remove the #N/A from my document and work around this problem. However, this formula is littered throughout my document and would be pretty tough to update for the 50-100 ranges effected. It could be done, but I'm looking for a faster way.

Here's an example of the forumla that sometimes returns #N/A:
=VLOOKUP(AK35,Availability!$C$5:$EM$54, IF('Blank Bid'!$AA$14="HOT PIT", 44, 43), 0)

If the value at AK35 is blank, then a #N/A is returned.

 

[nomar116]

I came up with this modification that seems to be working. Sorry for the multiple replies, I don't seem to be able to edit previous replies.

Public Function ColorCountIf(ByRef MyColor As Range, ByRef MyRange As Range, ByVal MyValue As String) As Long
    
    clr = MyColor.Interior.Color
    
    For Each c In MyRange
        If IsError(c.Value) Then
        ElseIf c.Interior.Color = clr And c.Value = MyValue Then ColorCountIf = ColorCountIf + 1
        End If
    Next c

End Function

 

[nomar116]

This is a great start at what I'm trying to do. Here are a few follow up questions:

1. I would like to count the first name in a column of colored cells. Using Eric's previous example and the updated image:

I'm interested in modifying the previous VBA code such that =colorleadercountif(A1,A1:H20,"al") would return 1, located at C2.

2. Any thoughts on ways to speed these calculations up? I'm guessing it will take 1-2 minutes to recalculate one I scale this up to the level required for my worksheet.

3. If I can speed up the code, how could I get this auto-recalculate? Otherwise, I might add a macro enabled button so the user doesn't need to know to press F9.