Solved: % using the color of a cell

[Ger]

I'm using a dutch excell version
I want to get a % by using the color of a cell. I have white, green en red cells.
I want the next formula

red cells/(red cells + green cells)

I found the next formula

=sumproduct(--(colorindex(range)=3)) that must count the red cells but as i translated it to

= somproduct(--(kleurindex(range)=3)) i get #Naam?

what is wrong?

Ger

[Bob Phillips]

Colorindex is a UDF Ger, so that should not be translated.

Have you put the UDF code in your workbook?

[Ger]

=SOMPRODUCT(--(colorindex(E6:E15)=4))
also gives #naam?
What do you mean by putting the UDF code in the workbook?
I only put this formala in a cell

Ger

[OTWarrior]

Are the normal functions in excel in dutch, or english?
NB: I couldn't get the colourindex thing to work.

or example do these work?

[VBA]=SUM(A1:A4)[/VBA]
or
[VBA]=COUNT(A1:A4)[/VBA]

[Ger]

all formulas in cells are in dutch.

[OTWarrior]

i only ask as i coudln't get:
=sumproduct(--(colorindex(range)=3))
to work at all, or should there be some values where the -- are?

[Bob Phillips]

Colorindedx is a UDF, so you have to put the code in the workbook. Excel does not have a ColorIndex function built in, Where did you get the formula from, was it http://www.xldynamic.com/source/xld.ColourCounter.html?

[OTWarrior]

UDF = User Defined Function?

[Bob Phillips]

Yes.

[Zack Barresse]

These functions might help...

[vba]Public Function SUMCOLOR(rCheck As Range, _
Optional iColorIndex As Long = -4142, _
Optional iFontIndex As Long = -4105) _
As Variant
Dim zCell As Range, iTemp As Variant
For Each zCell In rCheck.Cells
If zCell.Interior.ColorIndex = iColorIndex Or zCell.Interior.ColorIndex = -4142 Then
If iColorIndex <> zCell.Interior.ColorIndex Then GoTo Skip_zCell
If zCell.Font.ColorIndex = iFontIndex Or zCell.Font.ColorIndex = -4105 Then
If iFontIndex <> zCell.Font.ColorIndex Then GoTo Skip_zCell
If IsNumeric(zCell.Value) Then iTemp = iTemp + zCell.Value
End If
End If
Skip_zCell:
Next
SUMCOLOR = 0
If Not IsEmpty(iTemp) Then SUMCOLOR = iTemp
End Function

Public Function COUNTCOLOR(rCheck As Range, _
Optional iColorIndex As Long = -4142, _
Optional iFontIndex As Long = -4105) _
As Variant
Dim zCell As Range, iTemp As Variant
For Each zCell In rCheck.Cells
If zCell.Interior.ColorIndex = iColorIndex Or zCell.Interior.ColorIndex = -4142 Then
If iColorIndex <> zCell.Interior.ColorIndex Then GoTo Skip_zCell
If zCell.Font.ColorIndex = iFontIndex Or zCell.Font.ColorIndex = -4105 Then
If iFontIndex <> zCell.Font.ColorIndex Then GoTo Skip_zCell
If IsNumeric(zCell.Value) Then iTemp = iTemp + 1
End If
End If
Skip_zCell:
Next
COUNTCOLOR = 0
If Not IsEmpty(iTemp) Then COUNTCOLOR = iTemp
End Function

Function GETINDEXCOLOR(rCheck As Range) As Long
If rCheck.Cells.Count > 1 Then Exit Function
GETINDEXCOLOR = rCheck.Interior.ColorIndex
End Function

Function GETFONTCOLOR(rCheck As Range) As Long
If rCheck.Cells.Count > 1 Then Exit Function
GETFONTCOLOR = rCheck.Font.ColorIndex
End Function[/vba]

Use the two bottom functions (GETINDEXCOLOR, GETFONTCOLOR) to get the index number of the color (fill and font). (Originally done here.)

HTH

[Ger]

I copied the udf to the worksheet but without any result.

So i posted an example.

It is used to calculated how many trains in a serie this week a started in time.
If there is input in a cell it means the train was to late (red cell).
Date the are to come are white (see week 12)

Ger

[Bob Phillips]

You didn't, there is no ColorIndex function in that workbook. But I notice that you have merged cells, it isn't designed to work with merged cells.

[Ger]

I'm confused. If i look with vba in the worksheet i see the thingi copied from above. Is this the wrong place.
please help.

[Bob Phillips]

You copied Zacks functions into THisWorkbook (which practically makes them unaccesible), but the workbook is calling a UDF you have not implemented.

[Zack Barresse]

You need to take the code out of the ThisWorkbook module, and place it into a standard module, i.e. your Module1. Then your Sumproduct() formula could be reduced to ...

=IF(CountColor(D6:D15,3)=0,1,CountColor(D6:D15,4)/CountColor(D6:D15,3))

Unmerge your cells. Then explain in detail what you are trying to sum/count and under what conditions (not sure if you are summing or counting).

[Ger]

Ok,

i unmerged the cells, copied the vba code to the module sheet and changed the , into ; in the formula =ALS(CountColor(G6:G10;3)=0;1;CountColor(G6:G10;4)/(CountColor(G6:G10;3)+CountColor(G6:G10;4))).

But the answer is always 100%. See the example.



Ger

[Bob Phillips]

LOL!It is not cell colouring that you have used, it is conditional formatting.

You either need to check the CF conditions, or use this technique http://www.xldynamic.com/source/xld.CFConditions.html

[Ger]

It is to difficult for a simple user as i'm. So thanks for all the help.

Ger

[Bob Phillips]

As I said, you can do it by testing the same conditions as the CF

=SUMPRODUCT(--(D$6:D$10=""),--($A$1>=B$6:B$10))/SUMPRODUCT(--((D$6:D$10="")+(D$6:D$10>"")))

in cell D11

[Ger]

Thanks, this works.

I'm a simple user, can you explain where the -- stands for?

Ger