Re: ]] XL-mania [[ Jumlah Berdasar WARNA

 

Biar tambah pusing, eh tambah tertarik dengan macro

Coba,

Sub KolorSape()
'anton suryadi 26/Mar/2010
'Menghitung jumlah warna pada shapes
Application.ScreenUpdating = False
Dim p As Shape, q As Shape, i As Integer, j As Integer, a As Range, b As Range, x
Cells([M2], [M6] + 1).Resize([M3] - [M2] + 1).ClearContents
For i = [M2] To [M3]
   For Each p In Shapes
       If Not Application.Intersect(p.TopLeftCell, Cells(i, [M6])) Is Nothing Then
            x = p.Fill.ForeColor.SchemeColor
            Cells(i, [M6] + 2) = x
        End If
    Next p
Next
For j = [M2] To [M3]
    For Each q In Shapes
        Set a = Cells([J2], [J6])
        Set b = Cells([J3], [J6])
        If Not Application.Intersect(q.TopLeftCell, Range(a, b)) Is Nothing Then
        If q.Fill.ForeColor.SchemeColor = Cells(j, [M6] + 2) Then
            Cells(j, [M6] + 1) = Cells(j, [M6] + 1) + 1
        End If
       End If
    Next q
Next
Cells([M2], [M6] + 2).Resize([M3]).ClearContents
Application.ScreenUpdating = True
End Sub

File terlampir


>semoga bermanfaat

---

From: siti Vi <setiyowati.devi@gmail.com>
To: XL-mania@yahoogroups.com
Sent: Thu, March 25, 2010 9:09:15 AM
Subject: Re: ]] XL-mania [[ Jumlah Berdasar WARNA

 

kalau yang diberi warna itu CELL nya (baik Font-nya atau BackGround /Interior-nya)

maka cells tsb dapat dihitung (bukan ditotal) berdasarkan warnanya .

( yg merah ada berapa bijik dst.. 'gitu loch...)

 

atetapi kalau anda membuat Drawing-Object SHAPE 'Oval' dan harus dihitung

(berdasarkan warnanya juga), saya kira lebih sulit, tapi bisa,  tidak akan dijawab dulu..

 

Gambar SHAPE Oval itu bisa diganti dengan Huruf "n" dlm Font Webdings , dgn fontsize 16.

Nah karena berupa DATA (huruf n) bukan berupa Object gambar, maka dihitungnya

lebih mudah misal dengan fungsi  (udf)

=CountFontKolor( $B$5:$B$33, F5)

aturan penulisan (sintaks nya, seperti COUNTIF yah ?..)

 

 

---

----- Original Message -----
From: M. Luthfi
To: XL-mania@yahoogroup s.com
Sent: Wednesday, March 24, 2010 10:45 AM
Subject: ]] XL-mania [[ Jumlah Berdasar WARNA

 

Dear Rekan rekan XL-Mania yang baik,
Mau minta bantuan nih
Bagaimana cara menjumlah berdasarkan warna?
Lebih jelas dapat dilihat pada lampiran
Terimakasih sebelumnya
Regards
M.Luthfi

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (7)

Recent Activity:

• New Members 78

Visit Your Group

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| momods pecas ndaheeee... :( :( :(                                 | 
| http://www.facebook.com/group.php?gid=37671048001&ref=mf          |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message,  melecehkan,  tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan  winzip  jika  perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke  |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau...  |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id]  |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___