Excel: Macro dan Conditional Formatting

30 November 2007 | 68 comments

Beberapa waktu lalu, saya membuat tulisan tentang macro Excel yang dapat mengubah angka menjadi teks. Salah seorang pengunjung lantas bertanya kegunaan lain dari macro. Saya jawab dengan contoh.

Misalnya kita punya berkas Excel yang berisi ratusan angka di setiap sel. Kita, misalnya, ingin memberi warna merah untuk angka yang nilainya negatif dan warna biru untuk angka yang bernilai lebih dari 100.

Saya katakan bahwa pekerjaan tersebut dapat dilakukan dengan cepat, cermat, dan mudah dengan menggunakan macro.

Seorang pengunjung yang lain kemudian bertanya, “Bagaimana cara membuatnya?”

Ya, bagaimana melakukannya? Dapatkah hal ini dikerjakan dengan macro? Jawabannya tentu saja dapat. Apakah cepat, cermat, dan mudah? Cermat sih jelas. Tetapi, cepat dan mudah itu relatif.

Setidaknya tak secepat dan semudah jika kita melakukannya dengan fitur yang sudah tersedia di Excel: Conditional Formating!

Sebagai contoh, bukalah sebuah berkas Excel yang masih kosong. Lalu isilah sel-sel pada range A1:E20 dengan angka yang bervariasi antara -50 sampai 200. Anda bisa mengetikkan angka-angka tersebut di setiap sel, atau gunakan fungsi =RANDBETWEEN(-50, 200) di salah satu sel, lalu menyalinnya ke sel yang lain. Fungsi ini¹ akan menghasilkan bilangan acak antara -50 dan 200.

Setelah itu, sorotlah semua sel yang berisi angka-angka tersebut, lalu klik menu Format > Conditional Formatting.

Di bagian Condition 1, pilih Cell Value Is di ruas pertama, less than di ruas kedua, dan di ruas ketiga ketikkan 0. Klik tombol Format… lalu pilihlah warna merah di ruas Color.

Lalu kliklah tombol Add >> dan bagian Conditional 2 akan muncul. Isilah dengan Cell Value Is, greater than, dan 100. Klik tombol Format… dan pilihlah warna biru di ruas Color.

Klik tombol OK dan — jreng, semua angka negatif akan berwarna merah dan angka yang lebih besar dari 100 akan berwarna biru.

Cobalah menekan tombol F9 di papan ketik untuk menjalankan kembali fungsi RANDBETWEEN sehingga menghasilkan angka yang berbeda.

Dengan Macro

Bagi Anda yang ingin tahu bagaimana melakukannya dengan macro, buatlah sebuah worksheet seperti pada contoh di atas. Anda bisa menggunakan fungsi =RANDBETWEEN(-50, 200).

Tekan tombol Alt + F11 di papan ketik. Di jendela Microsoft Visual Basic bagian VBAProject² klik-ganda ThisWorkbook.

Di bagian sebelah kanan yang terbuka, tulislah kode berikut ini.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    nCol = 1
    nRow = 1
    Cells.Font.ColorIndex = 0
    While Not IsEmpty(Cells(nRow, nCol))
        While Not IsEmpty(Cells(nRow, nCol))
            Cells(nRow, nCol).Select
            If Selection.Value < 0 Then
                Selection.Font.ColorIndex = 3
            End If
            If Selection.Value > 100 Then
                Selection.Font.ColorIndex = 5
            End If
	nCol = nCol + 1
        Wend
        nCol = 1
        nRow = nRow + 1
    Wend
End Sub

Kembalilah ke worksheet Anda untuk melihat hasilnya. Jika Anda menggunakan fungsi RANDBETWEEN, cobalah tekan tombol F9 berulang-ulang untuk menghasilkan angka yang berbeda.

Catatan. Conditional Formatting di Excel hanya menyediakan 3 kondisi. Bila Anda memiliki lebih dari tiga kondisi yang ingin dijalankan, Anda harus memakai macro.

1. Fungsi RANDBETWEEN bukan fungsi bawaan dari Excel. Anda harus mengaktifkannya dengan pergi ke Tools > Add-Ins. Centang Analysis ToolPak lalu klik tombol OK. CD instalasi MS Office mungkin diperlukan. [↩]
2. Bila bagian ini tidak ada, pilihlah View > Project Explorer atau tekan tombol Ctrl + R. [↩]

Tags: excel, macro, tips, tutorial

Entri yang Mungkin Terkait

• Macro Excel: Mengubah Angka Menjadi Teks
• Macro Excel Pengganti VLookup
• Mencari Nama Hari dari Sebuah Tanggal di Excel
• Ekstensi yang Saya Pakai di Firefox
• Kalender 2008

Komentar. Silakan berikan komentar Anda. Beberapa tag HTML diperbolehkan. Anda juga dapat mendaftar di Gravatar untuk menampilkan foto Anda.

1. 

   Panji on 4 June 2009 at 21:33

   Help numpang tanya nich..
   misalnya ada 2 kolumn negh gimana macronya biar bisa ngewarnain sel di kolom ke 2 dengan warna ap aj kalo2 ada yg nilainya sama sama yg ada di kolum ke 1 thanks

2. 

   maman s on 11 June 2009 at 08:14

   Terima kasih banyak. cara merubah warna tulisan dengan kondisi tertentu sudah lama saya cari, dan sekarang bisa saya dapatkan di sini. sekali lagi terima kasih.

3. 

   Undang on 3 July 2009 at 17:46

   pak, saya mo minta tolong ada ga rumus atau makro atau sejenisnya yang bisa menggantikan fungsi F9.. Maksudnya hurup itu bisa berjalan sendiri tanpa menekan F9.. Kalo ada Kasih tau.. apa namanya dan gimana caranya.. mohon dibalas.. terima kasih banyak

4. 

   Adh!e on 20 July 2009 at 17:24

   Salam Kenal….

   Mas Gimana Caranya Merubah Warna pada Grafik di Excel pada Kom atau Baris yang sama….

   Misalnya Grafik pada Kolom Pertama Warna Biru Semua… Kalau Saya ingin rubah menjadi Merah, Kuning, Biru bisa ga Mas tapi bukan manual lewat Macro….. Kalo Bisa Am Kode nya ya…

   Tolong Ya MAS untuk Tugas Saya…
   Terima Kasih….

5. 

   Mulyadi on 2 August 2009 at 20:18

   Help me.. PINDAH CELL TANPA MENEKAN PANAH KANAN atau ENTER

   Saya punya kasus Excell seperti ini:

   Saya punya banyak data masing masing berupa 1 digit angka.
   Angka angka tersebut ke dalam worksheet excel cukup dengan mengetikkan 1 angka lalu menekan enter atau panah kanan dan cursor akan berpindah ke cell berikutnya.

   Pertanyaan saya :
   Bagaimana cara supaya cursor otomatis berpindah ke cell berikutnya (ke kanan) setelah saya mengetikkan 1 angka, tanpa harus menekan enter atau panah kanan?

   Terima kasih responsnya..

6. 

   dimas on 13 August 2009 at 20:36

   malam mas…
   saia salah satu pengguna newbie excel mas..
   numpang tanya ya mas…
   saia coba bikin macro yang dihubungkan dengan chart dan sukurnya sukses, tapi ketika sheet nya saia proteksi, macronya jadi erorr katanya object not defined…
   saia dah coba unlock chart yang saia buat tapi hasilnya tetap sama…
   mohon bantuannya mas…

7. 

   roni on 14 September 2009 at 21:14

   bagaimana membuat macro Condition (lessthan) tapi yang muncul langsung berwarna.di excel 2007
   trims

8. 

   yuni yuniawati on 25 September 2009 at 07:25

   maf,kak, mau nanya pake randbetween di excel supaya angkanya gak rubah-rubah gimana caranya, mohon dibantu ya… cos ini tugas.y dkumpulin.y rabu,,, mksi

9. 

   richi kiswiyantoko on 29 November 2009 at 21:01

   saya ada pertanyaan :

   1. saya punya data tentang peternak sapi di daerah saya, tiap desa memiliki beberapa pos penampungan susu sapi,tiap pos penampungan punya kelpmpok anggota sendiri.dan tiap desa memiliki no anggota sendiri mulai dari satu lagi, misal desa A no anggota 1 = paijo, desa B no anggota 1 = joko, begitu seterusnya. begini masalahnya, di form input saya ingin buat

   tanggal : …………
   Desa :…………
   Pos :…………
   nomer anggota :…………..
   nama anggota :…………

   nah yang saya inginkan adalah bila dipilih daerah posnya otomatis di sel “desanya” sudah keluar nama desa tempat pos tersebut berada, dan di sel “nomer anggota” sudah otomatis pilihan di sel no anggota datanya adalah data anggota dari desa tersebut, dan kemompok Pos penampungan itu.
   di tabel bantu saya saya bikin dengan urutan
   kolom 1= data desa
   kolom 2= no anggota
   kolom 3=nama anggota
   kolom 4=pos
   tabel bantu itu beda Sheetnya.
   bagaimana rumusnya?

   2.untuk data grafiknya. data tersebut terpaku dari data base input. pertanyaanya jika dipilih desanya antara tanggal sekian sampai sekian otomatis keluar data di diagramnya data dari database tentang desa tersebut. misal ada data tentang keterangan “ya” dan “tidak” otomatis masuk di kolom yang sudah saya tentukan itu.

   misal kolomnya
   kolom 1 = kebersihan kandang
   2= kebersihan ember susu

   tolong dijawab kalau bisa sebelum tanggal 5 desember 2009 kalau tidak bisa tolong dijawab saja biar bisa saya pakai referensi. terima kasih.

10. 

    dIKI on 4 December 2009 at 19:25

    Permisi, saya mau nanya..kal d excel pengen bikin cheklist gimana y..misal, saya punya 5 list barang yang saya tulis di excel, kemudian disamping setiap list barang itu saya ingin kasih tampilan untuk menunjukan barang mana yang sudah ada atau belum ada..misal barang yang sudah ada saya cheklist, yang belum ada tidak saya checlist…terimakasih untuk jawabannya…

11. 

    syafir on 28 January 2010 at 13:10

    mas, dulu saya pernah meneemukan program ramalan yang terbuat dari excel. dalam program tersebut hanya menuliskan tanggal lahir teru setelah enter maka keluar tentang sifat sifat orang yang dimasukan tanggal lahirnya tersebut. itu gmana caranya! make fungsi apa? mhon dibalas! terimakasih!!!!

12. 

    syafir on 28 January 2010 at 13:13

    Nambah mas ! bagaimana cara mengtahui data yang double counting???? trims!

13. 

    huda on 13 February 2010 at 07:53

    terima kasih atas segala infonya,
    mau tanya lagi, yang mengubah angka menjadi teks tanpa kata rupiah bagaimana

14. 

    denni on 13 July 2011 at 19:40

    Trm ksh atas smua info’a yg sngat berguna bwt sya.
    Mau tanya gan, klo datanya berupa huruf dan lebih dari tiga, gmn penulisan macronya gan…?
    Terima kasih…………

15. 

    arif on 15 August 2011 at 14:54

    mas bagai mana cara bikin rumus :
    Misal D1 menunjukan nilai minus kan secara otomatis warna huruf merah terus nama jenis barang di kolom A1 ikut merah,tolong gimana caranya?

16. 

    Johan on 13 October 2011 at 23:23

    Mas mohon saya dibantu bagaimana agar cursor berpindah secara otomatis setelah memasukkan data 1 angka atau huruf ke sebelah kanannya tanpa menekan panah kanan

17. 

    said on 26 October 2011 at 09:37

    Help me.. PINDAH CELL TANPA MENEKAN PANAH KANAN atau ENTER

    Saya punya kasus Excell seperti ini:

    Saya punya banyak data masing masing berupa 1 digit angka.
    Angka angka tersebut ke dalam worksheet excel cukup dengan mengetikkan 1 angka lalu menekan enter atau panah kanan dan cursor akan berpindah ke cell berikutnya.

    Pertanyaan saya :
    Bagaimana cara supaya cursor otomatis berpindah ke cell berikutnya (ke kanan) setelah saya mengetikkan 1 angka, tanpa harus menekan enter atau panah kanan?

    Terima kasih responsnya..

18. 

    Gusti on 4 November 2011 at 22:20

    makasih banyak gan sangat membantu sukses terus

‹ Previous · 1 · 2