Here's a couple of previous posts on the subject
Nimrod
MrExcel MVP
Joined: 30 Apr 2002
Posts: 2511
Location: Vancouver BC , Canada
Status: Offline
Reply with quote
ColorSumIf...Version 2
In this version the Function takes the color from the cell its in. Th
only parameter you give to it is the number of the column you wan
evaluated:
For example if you want to have your answer in D2 for the Column C Re
Cells then you enter the following Function in D2:
=ColorSumIf(3)
AND make the color of D2 RED.
Here is the code for this version:
code:
Public Function ColorSumIf(Cm)
Do
rwIndex = rwIndex + 1
With Cells(rwIndex, Cm)
If .Interior.ColorIndex
ActiveCell.Interior.ColorIndex Then
ColorSumIf = ColorSumIf + .Value
End If
End With
Loop Until Len(Trim(Cells(rwIndex, Cm).Value)) = 0
End Function
Post Fri May 10, 2002 9:38 pm
View user's profile Send private message AIM Address
Lo Lun To
Board Regular
Joined: 10 May 2002
Posts: 8
Status: Offline
Reply with quote
I think a VBA solution would the best, but here's a non-VBA way.
- Define a name (lets say Clr) and type in the RefersTo bo
=GET.CELL(38,INDIRECT("rc[-1]",FALSE))
- Insert a new column immediately after column A.
- In the new column put in cells B1:B5 =Clr
Cells B1:B5 will then show the ref numbers for the fill-colours use
for A1:A5
You can then use the numbers in B1:B5 for your SUMIF formula.
Column B could be kept hidden.
Note : The formulas in column B will only update on a shee
recalculation
