Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

sum cells with conditional formatting color

  Asked By: Bonita    Date: Mar 07    Category: MS Office    Views: 3109
  

I don't know how to modify this function so that I can apply it to a range
with conditional formatting color
(Function works well with normal fill color cells)
Could you please help me ?

Function SumColor(rColor As Range, rSumRange As Range)

'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums cells based on a specified fill color.

Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
End If
Next rCell
SumColor = vResult
End Function

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Kristin Johnston     Answered On: Mar 07

As far as I'm aware, you can't write code to look at the conditional  colour
of a cell. That colour is the result of the conditional operation and not
an actual property of the cell.

But you don't need to. Simply do the same test in your summing that the
conditional test is doing. In fact, you shouldn't need VBA at all.

 
Answer #2    Answered By: Beatriz Silva     Answered On: Mar 07

it is complex but eminently possible. See
www.xldynamic.com/source/xld.CFConditions.html

 
Answer #3    Answered By: Yvonne Watkins     Answered On: Mar 07

Thanks for that. I now remember having seen it before, but I'd forgotten
about it. As you say, it's complex.

 
Didn't find what you were looking for? Find more on sum cells with conditional formatting color Or get search suggestion and latest updates.




Tagged:          

 
 
 
 

Related Post