Hi there good people!
I'm trying to get excel to count the number of cells in a range (column or row) after the cells have been coloured by conditional formatting rule that stipulates "if the value in the cell is higher than a certain value, the shade the cell in a colour ..." so to speak.
I have tried using VBA and created a formula below and (i think!) saved it in the C Drive / Microsoft / Adins folder ...
Is the script I am using an old one and has the color index changed or do I have to define it ... or what is not working??
I am getting a "0" in the return, which kind of suggests the formual is working, just not recognising the cells or cell colour?
SCRIPT (as follows) and then added as a User Defined function
Hopefully this screen shot will work, but basically the following shows the range first, then the reference cell, then the screen shot shows the result ...
<colgroup><col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;">
<tbody>
</tbody>
I'm trying to get excel to count the number of cells in a range (column or row) after the cells have been coloured by conditional formatting rule that stipulates "if the value in the cell is higher than a certain value, the shade the cell in a colour ..." so to speak.
I have tried using VBA and created a formula below and (i think!) saved it in the C Drive / Microsoft / Adins folder ...
Is the script I am using an old one and has the color index changed or do I have to define it ... or what is not working??
I am getting a "0" in the return, which kind of suggests the formual is working, just not recognising the cells or cell colour?
SCRIPT (as follows) and then added as a User Defined function
Code:
Function CountCcolor(range_data As Range, criteria AsRange) As Long
Dim datax AsRange
Dim xcolor AsLong
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
Ifdatax.Interior.ColorIndex = xcolor Then
CountCcolor= CountCcolor + 1
End If
Next datax
End Function
Sick Leave | |||||
21.75 | |||||
0 | |||||
0 | |||||
21.75 | |||||
39.75 | |||||
14 | |||||
37
|
Last edited by a moderator: