SUMIF Interior.ColorIndex = 3 1

[PWD]

I'm just trying to help a user colour various cells using the simple

Sub TestColourShading()
Selection.Interior.ColorIndex = 3
End Sub

because he wants to be able to do a conditional sum of these cells.
I've recorded a macro based on the SUMIF function

ActiveCell.FormulaR1C1 = _
"=SUMIF(R[1]C[-1]:R[3]C[-1],""Interior.ColorIndex = 3"")"

using the above logic but it just returns a 0 although it puts the following formula into the cell I7,
=SUMIF(H18:H20,"Interior.ColorIndex = 3")

I've had a quick look in the forum but can't see that anyone has done this; surely someone has! I can only assume that there's a difference between what VBA calls a cell colour and what Excel does.

Hope someone can assist. Many thanks, Des.

P.S. Hope no-one's upset by the English spelling of colour! LOL!!

 

[xlbo]

You cannot SUM cells based on the colour as a worksheet function. You can only do conditional sums based on the contents of a cell

To do what you want, you would have to create a custom SUM function eg.

Function SumColours(rng as range)
for each c in rng
 if c.interior.colorindex = 3 then
     SUMColours = SUMColours + c.value
 end if
next
End Function

You would then use this on the sheet by entering

=SumColours(A1:A10)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

There is no spreadsheet function that uses FORMATS to conditionally count or sum.

However, you could use this user defined function in the sheet, to accomplish this objective...

function SumIfInteriorColorIndex(rngSUM, nCondition)
  dim r as range
  for each r in rngsum
    if r.interior.colorindex = ncondition then
      SumIfInteriorColorIndex = SumIfInteriorColorIndex + r.value
    end if
  next
end function

Skip,

_[red][/red]

 

[PWD]

You guys!! Straight out of the blocks with your replies. Dang shame it won't do it the way I'd figured. Many thanks, Des.