SUM cell based on their interior color(Have fun with excel)

[syar]

Suppose agar aap k pass aik excel ki file ha Jis m 1000 memebers ki detail ha aur aap ne un ki amount ya salary etc k cell ko color deya huwa ha Aur aap chchte ho k sirf un cell ko sum kiya jae jis k color yellow ya green etc ho.
It is possible in Microsoft Excel. Leekan is k leye aap ko VBA (Macro) istimal karna ho gi. Jis k leye follow these steps

1. Press Alt+F11
2. Insert menu m ja kar module select karain aur open hone wale window m following code enter karain.

-------------------------------------------------------------------------
Function Sumbycolor(cellcolor As Range, sumrange As Range)
Dim mycell As Range
Dim icol As Integer
Dim mytotal
icol = cellcolor.Interior.ColorIndex 'get the target color
For Each mycell In sumrange 'look at each cell in the designated range
'if the cell color matches the target color
If mycell.Interior.ColorIndex = icol Then
'add the value in the cell to the total
mytotal = WorksheetFunction.Sum(mycell) + mytotal
End If
Next mycell
Sumbycolor = mytotal
End Function
-----------------------------------------------------------

Now you can use this function like this.

=sumbycolor(cellcolor,sumrange)

where cellcolor -------The address of a cell with taget color( yani jis cell k color ko base bana chahte hain.

sumrange------The range of cell to be summed. ( yani jis range ko aap jama karna chahte hain.

=sumbycolor(a1,a1:a10)

see the attached picture.

Note Jis cell ko conditional format keya ho wo cell pa ye apply nahe hoga. Cell k background color he sum honge. Font agar color ho ga tu sum nahee honga.

may be this will help
regards

[M-Qasim]

Superb Work

Nice Sharing .... Thanks for Sharing

[mani]

nice

[friend4allage]

nice

[syar]

Hosla afzaee k leye Aap sab a shukriya.

[Shehzad Iqbal]

wao
super work yaar
thanx for sharing

[jaadoogar]

gr888 work dear

[saeed pk]

well done

[Jaffary]

[zulfi721]

Good work,but bhai programing sy dur he lugta hy )sheet ka kya sy kya haal kr dy)sorry i m saying so because i m not familiar with programing.....any how aap ke sharing mazy ke hy...lets give it a try

[Muazzam Sameer]

good yar

[Ganxtr]

NYC....
Your thread is very useful, thanks for sharing with us.