Excel SUM colored cells 1

[VisaManiac]

I hope I'm posting this in the correct place. I've done some research and everyone is pointing me to use VBA for my excel funtion I'm searching for.

I have on column C my dollar values, and D ~ O are months Jan ~ Dec. In those monthly columns no value will be input, just blue colored cells, however I'd like for the bottom of each column to only give me the SUM pulled from column C where the color blue reside. For example...

| C | D | E |
$10 blue
$20 blue
$30 blue
$40 blue
TOTAL =40 =60

I've found several tutorials on how to do this, but I don't know VBA at all, so I didn't know how to make the code reference the correct cells or know what color to find. Here's one that seems relevant to what I'm looking for, I just am clueless how to change it around to fit my cell structure...

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

To call this function from a worksheet cell with a formula use
=SUMIFBYCOLOR(A1:A10,3,B1:B10,FALSE)

Any guidance on this would be greatly appreciated!!

 

[SkipVought]

Hi,

"To call this function from a worksheet cell with a formula use"
[tt]
=SUMIFBYCOLOR(A1:A10,3,$B1:$B10,FALSE)
[/tt]
You will want to make the SUM RANGE column ABSOLUTE, so that you can copy across. It looks to me as if your have the ranges backwards. I probably should be...
[tt]
=SUMIFBYCOLOR(B1:B10,3,$A1:$A10,FALSE)
[/tt]


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[VisaManiac]

I've tried pasting that exact code into VB and then in my cell typed in

=SUMIFBYCOLOR(C6:C21,3,D621,FALSE)

but the results is #NAME?

I think if someone can briefly explain to me in the code what areas I need to change to reference the right location in my spreadsheet, and the formula =SUMIFBYCOLOR(C6:C21,3,D621,FALSE) could tell me what each section of the formula means or need to point at (1st section C6:C21 should be my blue cells or the Dollar value, what does 3 mean, and where should that 3 section D621 point to) then it would help me out tremendously. Thanks again!!

 

[SkipVought]

First of all THREE 3, is RED.

You need to have this function in a MODULE, rather than a Sheet or Workbook object.

This explains how to use the function.

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

The function works just fine.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]