Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum colored cells
I have the following code in module1.
I`m trying to sum values in cells column D based on the color of cells in column E that are yellow? Function CellColorIndex(InRange As Range, Optional _ OfText As Boolean = False) As Integer ' ' This function returns the ColorIndex value of a the Interior ' (background) of a cell, or, if OfText is true, of the Font in the cell. ' Application.Volatile True If OfText = True Then CellColorIndex = InRange(1, 1).Font.ColorIndex Else CellColorIndex = InRange(1, 1).Interior.ColorIndex End If End Function Function SumIfByColor(InRange As Range, _ WhatColorIndex As Integer, SumRange As Range, _ Optional OfText As Boolean = False) As Variant ' ' This function will return the SUM of the values of cells in ' SumRange where the corresponding cell in InRange has a background ' color (or font color, if OfText is true) equal to WhatColorIndex. ' Dim OK As Boolean Dim Ndx As Long Application.Volatile True If (InRange.Rows.Count < SumRange.Rows.Count) Or _ (InRange.Columns.Count < SumRange.Columns.Count) Then SumIfByColor = CVErr(xlErrRef) Exit Function End If For Ndx = 1 To InRange.Cells.Count If OfText = True Then OK = (InRange.Cells(Ndx).Font.ColorIndex = WhatColorIndex) Else OK = (InRange.Cells(Ndx).Interior.ColorIndex = WhatColorIndex) End If If OK And IsNumeric(SumRange.Cells(Ndx).Value) Then SumIfByColor = SumIfByColor + SumRange.Cells(Ndx).Value End If Next Ndx End Function nothing happens?? -- AOP |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum colored cells
Have a look at one or more of these
www.ozgrid.com/VBA/Sum.htm www.cpearson.com/excel/colors.htm www.xldynamic.com/source/xld.ColourCounter.html or Google with term: excel sum by color ; to get other links best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "AOP" wrote in message ... I have the following code in module1. I`m trying to sum values in cells column D based on the color of cells in column E that are yellow? Function CellColorIndex(InRange As Range, Optional _ OfText As Boolean = False) As Integer ' ' This function returns the ColorIndex value of a the Interior ' (background) of a cell, or, if OfText is true, of the Font in the cell. ' Application.Volatile True If OfText = True Then CellColorIndex = InRange(1, 1).Font.ColorIndex Else CellColorIndex = InRange(1, 1).Interior.ColorIndex End If End Function Function SumIfByColor(InRange As Range, _ WhatColorIndex As Integer, SumRange As Range, _ Optional OfText As Boolean = False) As Variant ' ' This function will return the SUM of the values of cells in ' SumRange where the corresponding cell in InRange has a background ' color (or font color, if OfText is true) equal to WhatColorIndex. ' Dim OK As Boolean Dim Ndx As Long Application.Volatile True If (InRange.Rows.Count < SumRange.Rows.Count) Or _ (InRange.Columns.Count < SumRange.Columns.Count) Then SumIfByColor = CVErr(xlErrRef) Exit Function End If For Ndx = 1 To InRange.Cells.Count If OfText = True Then OK = (InRange.Cells(Ndx).Font.ColorIndex = WhatColorIndex) Else OK = (InRange.Cells(Ndx).Interior.ColorIndex = WhatColorIndex) End If If OK And IsNumeric(SumRange.Cells(Ndx).Value) Then SumIfByColor = SumIfByColor + SumRange.Cells(Ndx).Value End If Next Ndx End Function nothing happens?? -- AOP |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum colored cells
Where are you entering the formula that uses these Functions and what is the
formula? I assume some type of SUMIF formula entered in a cell as Chip illustrates You can call this function from a worksheet cell with a formula like =SUMIFBYCOLOR(A1:A10,3,B1:B10,FALSE) Maybe go back to Chip's site and re-read the instructions. http://www.cpearson.com/excel/colors.htm Gord Dibben MS Excel MVP On Thu, 8 Nov 2007 10:58:00 -0800, AOP wrote: I have the following code in module1. I`m trying to sum values in cells column D based on the color of cells in column E that are yellow? Function CellColorIndex(InRange As Range, Optional _ OfText As Boolean = False) As Integer ' ' This function returns the ColorIndex value of a the Interior ' (background) of a cell, or, if OfText is true, of the Font in the cell. ' Application.Volatile True If OfText = True Then CellColorIndex = InRange(1, 1).Font.ColorIndex Else CellColorIndex = InRange(1, 1).Interior.ColorIndex End If End Function Function SumIfByColor(InRange As Range, _ WhatColorIndex As Integer, SumRange As Range, _ Optional OfText As Boolean = False) As Variant ' ' This function will return the SUM of the values of cells in ' SumRange where the corresponding cell in InRange has a background ' color (or font color, if OfText is true) equal to WhatColorIndex. ' Dim OK As Boolean Dim Ndx As Long Application.Volatile True If (InRange.Rows.Count < SumRange.Rows.Count) Or _ (InRange.Columns.Count < SumRange.Columns.Count) Then SumIfByColor = CVErr(xlErrRef) Exit Function End If For Ndx = 1 To InRange.Cells.Count If OfText = True Then OK = (InRange.Cells(Ndx).Font.ColorIndex = WhatColorIndex) Else OK = (InRange.Cells(Ndx).Interior.ColorIndex = WhatColorIndex) End If If OK And IsNumeric(SumRange.Cells(Ndx).Value) Then SumIfByColor = SumIfByColor + SumRange.Cells(Ndx).Value End If Next Ndx End Function nothing happens?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum colored cells
So what do you put in the cell?
colorindex of yellow is 6 if you don't know how to install UDF or macros see http://www.mvps.org/dmcritchie/excel/install.htm -- Regards, Peo Sjoblom "AOP" wrote in message ... I have the following code in module1. I`m trying to sum values in cells column D based on the color of cells in column E that are yellow? Function CellColorIndex(InRange As Range, Optional _ OfText As Boolean = False) As Integer ' ' This function returns the ColorIndex value of a the Interior ' (background) of a cell, or, if OfText is true, of the Font in the cell. ' Application.Volatile True If OfText = True Then CellColorIndex = InRange(1, 1).Font.ColorIndex Else CellColorIndex = InRange(1, 1).Interior.ColorIndex End If End Function Function SumIfByColor(InRange As Range, _ WhatColorIndex As Integer, SumRange As Range, _ Optional OfText As Boolean = False) As Variant ' ' This function will return the SUM of the values of cells in ' SumRange where the corresponding cell in InRange has a background ' color (or font color, if OfText is true) equal to WhatColorIndex. ' Dim OK As Boolean Dim Ndx As Long Application.Volatile True If (InRange.Rows.Count < SumRange.Rows.Count) Or _ (InRange.Columns.Count < SumRange.Columns.Count) Then SumIfByColor = CVErr(xlErrRef) Exit Function End If For Ndx = 1 To InRange.Cells.Count If OfText = True Then OK = (InRange.Cells(Ndx).Font.ColorIndex = WhatColorIndex) Else OK = (InRange.Cells(Ndx).Interior.ColorIndex = WhatColorIndex) End If If OK And IsNumeric(SumRange.Cells(Ndx).Value) Then SumIfByColor = SumIfByColor + SumRange.Cells(Ndx).Value End If Next Ndx End Function nothing happens?? -- AOP |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum colored cells
Cells C1 to C4 contain values of 1,2,3,7 and cells D1 to D4 are coloured
yellow (value=6) In say E1 put: =sumifbycolor(D1:D4,6,C1:C4) Result is 13 HTH "AOP" wrote: I have the following code in module1. I`m trying to sum values in cells column D based on the color of cells in column E that are yellow? Function CellColorIndex(InRange As Range, Optional _ OfText As Boolean = False) As Integer ' ' This function returns the ColorIndex value of a the Interior ' (background) of a cell, or, if OfText is true, of the Font in the cell. ' Application.Volatile True If OfText = True Then CellColorIndex = InRange(1, 1).Font.ColorIndex Else CellColorIndex = InRange(1, 1).Interior.ColorIndex End If End Function Function SumIfByColor(InRange As Range, _ WhatColorIndex As Integer, SumRange As Range, _ Optional OfText As Boolean = False) As Variant ' ' This function will return the SUM of the values of cells in ' SumRange where the corresponding cell in InRange has a background ' color (or font color, if OfText is true) equal to WhatColorIndex. ' Dim OK As Boolean Dim Ndx As Long Application.Volatile True If (InRange.Rows.Count < SumRange.Rows.Count) Or _ (InRange.Columns.Count < SumRange.Columns.Count) Then SumIfByColor = CVErr(xlErrRef) Exit Function End If For Ndx = 1 To InRange.Cells.Count If OfText = True Then OK = (InRange.Cells(Ndx).Font.ColorIndex = WhatColorIndex) Else OK = (InRange.Cells(Ndx).Interior.ColorIndex = WhatColorIndex) End If If OK And IsNumeric(SumRange.Cells(Ndx).Value) Then SumIfByColor = SumIfByColor + SumRange.Cells(Ndx).Value End If Next Ndx End Function nothing happens?? -- AOP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting colored cells | Excel Discussion (Misc queries) | |||
Select only colored cells | Excel Worksheet Functions | |||
count colored cells? | Excel Worksheet Functions | |||
Totaling Colored Cells | Excel Discussion (Misc queries) | |||
Count non-colored cells | Excel Discussion (Misc queries) |