Pemformatan Bersyarat VBA

Pemformatan Bersyarat di Excel VBA

Kita bisa menerapkan pemformatan bersyaratTerapkan Pemformatan Bersyarat Pemformatan bersyarat adalah teknik di Excel yang memungkinkan kita memformat sel di lembar kerja berdasarkan kondisi tertentu. Ini dapat ditemukan di bagian gaya pada tab Beranda. Baca lebih lanjut sel atau rentang sel di Excel. Conditional format adalah format yang diterapkan hanya pada sel yang memenuhi kriteria tertentu, misalkan nilai di atas nilai tertentu, nilai positif atau negatif, atau nilai dengan formula tertentu, dll. Pemformatan kondisional ini juga dapat dilakukan dalam pemrograman Excel VBA menggunakan makro/prosedur ‘ FormatConditions collection ‘.

FormatConditions mewakili format kondisional yang dapat diatur dengan memanggil metode yang mengembalikan variabel dari tipe tersebut. Ini berisi semua format bersyarat untuk rentang tunggal dan hanya dapat menampung tiga kondisi format.

FormatConditions.Add/Modify/Delete digunakan di VBA untuk menambahkan/memodifikasi/menghapus objek FormatCondition ke koleksi. Objek FormatCondition mewakili setiap format. FormatConditions adalah properti dari objek Range, dan Tambahkan parameter berikut dengan sintaks di bawah ini:

FormatConditions.Add (Tipe, Operator, Formula1, Formula2)

Sintaks rumus Tambahkan memiliki argumen berikut:

Jenis: Diperlukan. Ini mewakili format bersyarat berdasarkan nilai yang ada di sel atau ekspresi.
Operator: Opsional. Ini mewakili nilai operator saat ‘Tipe’ didasarkan pada nilai sel.
Formula1: Opsional. Ini mewakili nilai atau ekspresi yang terkait dengan format bersyarat.
Formula2: Opsional. Ini mewakili nilai atau ekspresi yang terkait dengan bagian kedua dari format bersyarat ketika parameter: ‘Operator’ adalah ‘xlBetween’ atau ‘xlNotBetween.’

FormatConditions.Modify juga memiliki sintaks yang sama dengan FormatConditions.Add.

Berikut ini adalah daftar beberapa nilai/pencacahan yang dapat diambil oleh beberapa parameter ‘Tambah’/’Ubah’:

Contoh Pemformatan Bersyarat VBA

Di bawah ini adalah contoh pemformatan bersyarat di Excel VBA.

Contoh 1

Kami memiliki file Excel yang berisi beberapa nama dan nilai siswa. Kami ingin menentukan/menyorot tanda sebagai “Bold” dan “blue”, yang lebih besar dari 80. “Bold” dan “Red”, yang kurang dari 50. Mari kita lihat data yang terkandung dalam file:

Kami menggunakan FormatConditions. Tambahkan fungsi di bawah ini untuk melakukannya:

Pergi ke Pengembang -> Editor Visual Basic:

Klik kanan pada nama buku kerja di panel ‘Project-VBAProject’-> ‘Insert’-> ‘Module.’

Sekarang tulis kode/prosedur dalam modul ini:

Kode:

Sub pemformatan() Akhir Sub

Tentukan variabel rng, kondisi1, kondisi2:

Kode:

Sub pemformatan() Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition End Sub

Tetapkan/perbaiki rentang yang diperlukan pemformatan bersyarat menggunakan fungsi ‘Rentang’ VBA:

Kode:

Sub pemformatan() Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range(“B2”, “B11”) End Sub

Hapus/hapus pemformatan bersyarat yang ada (jika ada) dari rentang, menggunakan ‘FormatConditions.Delete’:

Kode:

Sub pemformatan() Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range(“B2”, “B11”) rng.FormatConditions.Delete End Sub

Sekarang, tentukan dan tetapkan kriteria untuk setiap format bersyarat, menggunakan ‘FormatConditions.Add’:

Kode:

Sub pemformatan() Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range(“B2”, “B11”) rng.FormatConditions.Delete Set condition1 = rng.FormatConditions.Add(xlCellValue, xlGreater, “=80 “) Tetapkan condition2 = rng.FormatConditions.Add(xlCellValue, xlLess, “=50”) End Sub

Tentukan dan atur format yang akan diterapkan untuk setiap kondisi.

Salin dan tempel kode ini ke modul kelas VBA AndaModul Kelas VBAPengguna memiliki kemampuan untuk membangun Objek VBA mereka sendiri di Modul Kelas VBA. Objek yang dibuat dalam modul ini dapat digunakan dalam proyek VBA apa pun.Baca lebih lanjut.

Kode:

Sub pemformatan() ‘Mendefinisikan variabel: Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition ‘Memperbaiki/Menyetel rentang di mana pemformatan bersyarat diinginkan Set rng = Range(“B2”, “B11”) ‘To hapus/hapus semua pemformatan bersyarat yang ada dari rentang rng.FormatConditions.Delete ‘Mendefinisikan dan menyetel kriteria untuk setiap format bersyarat Tetapkan condition1 = rng.FormatConditions.Add(xlCellValue, xlGreater, “=80”) Tetapkan condition2 = rng.FormatConditions. Add(xlCellValue, xlLess, “=50”) ‘Mendefinisikan dan mengatur format yang akan diterapkan untuk setiap kondisi Dengan kondisi1 .Font.Color = vbBlue .Font.Bold = True Akhiri Dengan kondisi2 .Font.Color = vbRed .Font. Bold = True End Dengan End Sub

Sekarang, saat kita menjalankan kode ini menggunakan tombol F5 atau secara manual, kita melihat bahwa tanda yang kurang dari 50 disorot dengan huruf tebal dan merah, sedangkan yang lebih besar dari 80 disorot dengan huruf tebal dan biru sebagai berikut:

Catatan: Beberapa properti untuk tampilan sel berformat yang dapat kita gunakan dengan FormatCondition adalah:

Contoh #2

Katakanlah dalam contoh di atas. Kami memiliki kolom lain yang menyatakan bahwa siswa tersebut adalah ‘Topper’ jika skornya lebih dari 80. Selain itu, tertulis Lulus/Gagal untuk mereka. Sekarang, kami ingin menyorot nilai yang dinyatakan sebagai ‘Topper’ sebagai “Bold” dan “Blue”. Mari kita lihat data yang terkandung dalam file:

Dalam hal ini, kode/prosedur akan berfungsi sebagai berikut:

Kode:

Sub TextFormatting() Akhir Sub

Tentukan dan atur format yang akan diterapkan untuk setiap kondisi

Kode:

Sub TextFormatting() Dengan Range(“c2:c11″).FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:=”topper”) Dengan .Font .Bold = True .Color = vbBlue Akhiri Dengan Akhiri Dengan Sub Akhir

Kita dapat melihat pada kode di atas yang ingin kita uji jika range: ‘C2:C11″ berisi string: “Topper”, jadi parameter: “Onamestor” dari ‘Format.Add’ menggunakan enumerasi:” Xcontains” menjadi uji kondisi ini dalam kisaran tetap (yaitu, C2:C11). Kemudian, lakukan pemformatan bersyarat (perubahan font) yang diperlukan pada rentang ini.

Sekarang ketika kita menjalankan kode ini secara manual atau dengan menekan tombol F5, kita melihat bahwa nilai sel dengan ‘Topper’ disorot dengan warna biru dan tebal:

Catatan: Dalam dua contoh di atas, kita telah melihat cara kerja metode ‘Tambah’ jika ada kriteria nilai sel apa pun (numerik atau string teks).

Di bawah ini adalah beberapa contoh/kriteria lain yang dapat kami gunakan untuk menguji dan dengan demikian menerapkan pemformatan bersyarat VBA:

Format berdasarkan Periode Waktu
Kondisi rata -rata
Kondisi Skala Warna
Kondisi IconSet
Kondisi databar
Nilai Unik
Nilai Duplikat
Nilai 10 teratas
Kondisi Persentil
Kondisi Kosong, dll.

Dengan kondisi berbeda untuk diuji, nilai/pencacahan berbeda diambil oleh parameter ‘Tambah’.

Hal yang Perlu Diingat Tentang Pemformatan Bersyarat VBA

Kita dapat menggunakan metode ‘Tambah’ dengan ‘FormatConditions’ untuk membuat format bersyarat baru, metode ‘Hapus’ untuk menghapus format bersyarat apa pun, dan metode ‘Ubah’ untuk mengubah format bersyarat yang ada.
Metode ‘Add’ dengan ‘FormatConditions Collection’ gagal jika kita membuat lebih dari tiga format bersyarat untuk satu rentang.
Untuk menerapkan lebih dari tiga format bersyarat ke suatu rentang menggunakan metode ‘Tambah’, kita dapat menggunakan ‘Jika’ atau ‘pilih huruf’.
Jika metode ‘Add’ memiliki parameter ‘Type’ sebagai: ‘xlExpression’, maka parameter ‘Operator’ akan diabaikan.
Parameter: ‘Formula1’ dan ‘Formula2’ dalam metode ‘Add’ dapat berupa referensi sel, nilai konstanta, nilai string, atau bahkan formula.
Parameter: ‘Formula2’ hanya digunakan jika parameter: ‘Operator’ adalah ‘xlBetween’ atau ‘xlNotBetween,’ atau diabaikan.
Untuk menghapus semua pemformatan bersyarat dari lembar kerja apa pun, kita dapat menggunakan metode ‘Hapus’ sebagai berikut:

Cells.FormatConditions.Delete

Artikel yang Direkomendasikan

Artikel ini telah menjadi panduan untuk Pemformatan Bersyarat VBA. Di sini, kita belajar cara menerapkan pemformatan bersyarat ke sel Excel menggunakan metode Kondisi Format di VBA, contoh praktis, dan templat yang dapat diunduh. Di bawah ini Anda dapat menemukan beberapa artikel Excel VBA yang berguna: –