Hi,

The color and colorindex of a cell are not the same as the colour of a cell
arrived at by conditional formatting and summing by the latter isn't easy.
The best method I know of is on the xldynamic site but tonight the site is
down and you may choose to look tomorrow

http://xldynamic.com/source/xld.ColourCounter.html

Chip pearson has some methods that i've never used but knowing the stuff he
produces I doubt you'll go far wrong there.

http://www.cpearson.com/excel/CFColors.htm

Mike



"Khoshravan" wrote:

My question: How do I use conditional formatting in Excel to sum highlighted
cells?
Answer: I find the following macro in the internet:
source: http://answers.google.com/answers/th.../id/67275.html
However it doesn't work and gives #value error. What is the problem with
this user-defined function?
If you have a better solution, let me know.

Function CFmt(RangeInQuotes, ColorIndex)

Dim Total As Double
Set Acell = Range(RangeInQuotes)
'Loop each cell in the range and if cell background eq to color index
sum it
For Each cell In Acell
If cell.Interior.ColorIndex = ColorIndex Then
Total = Total + cell.Value
End If
Next

CFmt = Total
End Function