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

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?

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

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

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.

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

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

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

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.

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 © 2020 vBulletin Solutions Inc. All rights reserved.