Excel 2010 VBA ColorFunction with Countif

[apinke1604]

Hi,

Sorry if this has been asked before - I did find a similar thread with an answer, but I can't work out how to adjust it to fit what I need! (trackback incl.)

I have a table of people with their employee type and level (with the level cells RAG-rated according to their score)

	A	B
1	New/Experienced/Advanced	Level
2	N	1.2
3	E	1.5
4	A	3.1

<tbody>
</tbody>

I need to count:
1. The number of people with an N in column A and a Red level in column B
2. The number of people with an E or A in column A and a Red level in column B

Just to throw a spanner in the works - the RAG tolerance for the level is not standard across all the people, it is dependent on a number of other criteria including their employee type e.g. tolerances will be lower for a New-starter than for an Experienced or Advanced employee, so a level of 1.2 may be Red for one person but Amber for another if that makes sense? This is why I need to count by the colour of the cell rather than by the value.

I am using ColorFunction to count the number of Red cells in column B, however I can't work out how to add to this to include my other criteria.

Hope someone can help! Thanks in advance

 

[Parkour18]

I'm not sure if this is exactly what you want. It will work, but perhaps not in the most convenient way.

The only way that I've found to access the font color of a cell is by using a "Name" (on formula tab, click on name manager, click new.., I named mine "FontColor")
and setting the "Refers to:" section to the following formula: =GET.CELL(24,OFFSET(INDIRECT("RC",FALSE),0,-1))

Then by placing "=FontColor" (or whatever name you typed) in a cell, it will display a value corresponding to a color.

So, describing the FontColor reference:
Firstly, you should know this requires a workbook to have macros enabled and thus must be saved as a ".xlsm"
I believe the need for macro-enablement comes with the Get.Cell function, and the 24 tells the function to return a value pertaining to the font color. The second needed info is the reference.
The INDIRECT("RC",false) somehow corresponds to the cell in which Fontcolor is used in. The OFFSET then offsets the reference (in this case 0 rows, and -1 column)

Overview, What you'll need to do is create this name with specified equation. Then create an addition column, and place "=fontcolor" in the cells. if you have the column right next to the colored font you need a number for, then the offset will have 0,-1 (if 2 columns over, then 0,-2 etc.).


Now for the summing,
Lets say the fontcolor column cell value of 5 corresponds to red. Then, the total of people with N and Red level can be totaled with this =countifs(A1:A100,"=N",C1:C100,"=5")
,assuming your data is from A1:A100 and C1:C100.

Note: this is not case sensitive

 

[apinke1604]

Hi,
Thanks for your reply

It is the cell colour rather than the font colour that I need to reference - I've been using colorfunction for that, I just can't work out a way to use it with countif.
I was trying to avoid using the actual tolerances in a formula, but I think I might have to!