VBA formula for 2 different functions

[savannah84]

Hi Mr. Excel, I am attempting to write 2 different function codes and am requesting help, please. If I could merge both of these functions that would be even better! I will list the coding below, however, I am wanting to have the sum of only the yellow background highlighted cell values be placed within the active column on the active sheet on a specific row number.

Function ReturnRowNumber(ActiveCell As ReturnRowNumber, RowNumber As ReturnRowNumber)
Dim LR As Long, LC As Long
LR = ActiveCell.End(xlDown).Row
LC = ActiveCell.End(xlToRight).Column
Range(ActiveCell, Cells(LR, LC)).Select
End Function

Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIdex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next ReturnRowNumber
SumByColor = cSum
End Function



TYIA!

 

[Roderick_E]

Maybe explain a bit what each function is doing. Looks like the first is selecting a specific cell whereas the second it counting cell color in a range? The issue will be in merging the args from your functions.
Also will you/should you consider conditional formatted colors? If so, refer to .displayformat.interior.colorindex

 

[savannah84]

Hi Roderick, Thank you for your response. I don't want to use conditional formatting since these cells/values have already been purposefully selected. I have a ss (spreadsheet) with about 2000 rows and 118 columns. I want to only sum the cells with a background color as well as have that sum total be at the end of the range column instead of inserting another column for that sum to be placed. Does that make more sense??