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

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

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

Have you put the UDF code in your workbook?

=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)

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?

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?

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

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

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.

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:

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).

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

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

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

Ger

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

Thanks, this works.

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

Ger

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.

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

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.