Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct by cell color
I have the following code to sum cells by cell color, however i need to take
it one step further by using sum product by cell color. i would like to sum the numbers in the colored cells and then multiply by other cells that dont have to have a specific color. can someone please help me out? Thanks Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200811/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct by cell color
Introduce a parameter OFFSET
multiply each cell with the cell at that offset and then SUM If you want it to work on Rows and Column both then add DIRECTION also and calculate offset beased on that parameter... "brownti" wrote: I have the following code to sum cells by cell color, however i need to take it one step further by using sum product by cell color. i would like to sum the numbers in the colored cells and then multiply by other cells that dont have to have a specific color. can someone please help me out? Thanks Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200811/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct by cell color
Hi,
Here is code that basically does the same thing Function CountFormats(R As Range, E As Range) As Integer Dim cell As Range Dim Total As Integer Dim T As Boolean Set S = E.Cells(1, 1) Total = 0 For Each cell In R T = True With cell If .Interior.ColorIndex < S.Interior.ColorIndex Then T = False End With If T = True Then Total = Total + 1 * Cells(cell.Row, 12) End If Next cell CountFormats = Total End Function The key line relative to your question is Total = Total +1*Cells(cell.Row,12) If this helps, please click the Yes button Cheers, Shane Devenshire "brownti" wrote: I have the following code to sum cells by cell color, however i need to take it one step further by using sum product by cell color. i would like to sum the numbers in the colored cells and then multiply by other cells that dont have to have a specific color. can someone please help me out? Thanks Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200811/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't format cell color/text color in Office Excel 2003 in fil | Excel Discussion (Misc queries) | |||
Make text color match cell color with macro? | Excel Discussion (Misc queries) | |||
nested color index within sumproduct | Excel Worksheet Functions | |||
Excel: Syntax to change cell color based on color of another cell | Excel Worksheet Functions | |||
Can't format cell color/text color in Office Excel 2003 in files . | Excel Discussion (Misc queries) |