VBA code help to sum values in conditionally formated cells

[tgprasad85]

Hi Experts,

Need help in converting the below Sub to Function


Sub SumCountByConditionalFormat()
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Dim sumRes
Dim cntCells As Long
Dim indCurCell As Long

sumRes = 0

cntCells = Selection.CountLarge
indRefColor = ActiveCell.DisplayFormat.Interior.Color

For indCurCell = 1 To (cntCells - 1)
If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
End If
Next
MsgBox sumRes
End SubHi Experts,

[SamT]

An exact answer to your exact request. But it won't help you.


Function SumCountByConditionalFormat() '<----Changed
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Dim sumRes
Dim cntCells As Long
Dim indCurCell As Long

sumRes = 0

cntCells = Selection.CountLarge
indRefColor = ActiveCell.DisplayFormat.Interior.Color

For indCurCell = 1 To (cntCells - 1)
If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
End If
Next
MsgBox sumRes
End Function '<----Changed

Perhaps you can expand your post to answer "What are you trying to accomplish?"

[tgprasad85]

Hi,
I need to sum the columns based on a color which is derived from conditionally formatting

I see that you have changed the sub to function but what are the parameters that need to be entered in ()
Function SumCountByConditionalFormat(What parameters needs to be provided here???)

Additionally, the output of the Sub is a message box where as I need a reusable function
e.g. in CellB2 I thould be able to enter the formula "=SumCountByConditionalFormat()