PDA

View Full Version : Solved: % using the color of a cell



Ger
03-14-2008, 03:04 AM
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
03-14-2008, 03:18 AM
Colorindex is a UDF Ger, so that should not be translated.

Have you put the UDF code in your workbook?

Ger
03-14-2008, 03:22 AM
=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
03-14-2008, 03:57 AM
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?

=SUM(A1:A4)
or
=COUNT(A1:A4)

Ger
03-14-2008, 04:02 AM
all formulas in cells are in dutch.

OTWarrior
03-14-2008, 04:05 AM
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
03-14-2008, 04:26 AM
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
03-14-2008, 05:29 AM
UDF = User Defined Function?

Bob Phillips
03-14-2008, 05:50 AM
Yes.

Zack Barresse
03-14-2008, 07:31 AM
These functions might help...

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

Use the two bottom functions (GETINDEXCOLOR, GETFONTCOLOR) to get the index number of the color (fill and font). (Originally done here (http://forums.techguy.org/business-applications/689454-solved-excel-2007-colour-conditions-3.html).)

HTH

Ger
03-17-2008, 06:58 AM
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
03-17-2008, 08:22 AM
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
03-17-2008, 10:31 AM
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.:( :banghead: : pray2:

Bob Phillips
03-17-2008, 11:09 AM
You copied Zacks functions into THisWorkbook (which practically makes them unaccesible), but the workbook is calling a UDF you have not implemented.

Zack Barresse
03-17-2008, 11:10 AM
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
03-18-2008, 12:34 AM
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.

:help

Ger

Bob Phillips
03-18-2008, 02:19 AM
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
03-18-2008, 05:28 AM
It is to difficult for a simple user as i'm. So thanks for all the help.

Ger

Bob Phillips
03-18-2008, 05:45 AM
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
03-18-2008, 07:03 AM
Thanks, this works.

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

Ger

Bob Phillips
03-18-2008, 07:08 AM
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.

Ger
03-18-2008, 07:37 AM
Thanks for your sollution.:clap:

I think I understand what your formula does, so i changed the formula a bit and i get the result i want. I used the same formula in column M with succes.

I attached the example if someone else needs it.


thanks a lot again.


Ger
:thumb