Excel: Macro dan Conditional Formatting

Beberapa waktu lalu, saya membuat tulisan tentang macro Excel yang dapat mengubah angka menjadi teks. Salah seorang pengunjung lantasbertanya 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 fungsiRANDBETWEEN 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