Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 22

Thread: Solved: % using the color of a cell

  1. #1
    VBAX Contributor
    Joined
    Feb 2008
    Posts
    193
    Location

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Colorindex is a UDF Ger, so that should not be translated.

    Have you put the UDF code in your workbook?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Contributor
    Joined
    Feb 2008
    Posts
    193
    Location
    =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

  4. #4
    VBAX Mentor OTWarrior's Avatar
    Joined
    Aug 2007
    Location
    England
    Posts
    389
    Location
    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]
    -Once my PC stopped working, so I kicked it......Then it started working again

  5. #5
    VBAX Contributor
    Joined
    Feb 2008
    Posts
    193
    Location
    all formulas in cells are in dutch.

  6. #6
    VBAX Mentor OTWarrior's Avatar
    Joined
    Aug 2007
    Location
    England
    Posts
    389
    Location
    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?
    -Once my PC stopped working, so I kicked it......Then it started working again

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Mentor OTWarrior's Avatar
    Joined
    Aug 2007
    Location
    England
    Posts
    389
    Location
    UDF = User Defined Function?
    -Once my PC stopped working, so I kicked it......Then it started working again

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Yes.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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

  11. #11
    VBAX Contributor
    Joined
    Feb 2008
    Posts
    193
    Location

    I don't understand it

    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

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  13. #13
    VBAX Contributor
    Joined
    Feb 2008
    Posts
    193
    Location
    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.

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    You copied Zacks functions into THisWorkbook (which practically makes them unaccesible), but the workbook is calling a UDF you have not implemented.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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).

  16. #16
    VBAX Contributor
    Joined
    Feb 2008
    Posts
    193
    Location

    almost there

    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

  17. #17
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  18. #18
    VBAX Contributor
    Joined
    Feb 2008
    Posts
    193
    Location
    It is to difficult for a simple user as i'm. So thanks for all the help.

    Ger

  19. #19
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  20. #20
    VBAX Contributor
    Joined
    Feb 2008
    Posts
    193
    Location
    Thanks, this works.

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

    Ger

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •