Pemformatan Bersyarat VBA

Pemformatan Bersyarat di Excel VBA

Kita dapat menerapkan pemformatan bersyarat ke sel atau rentang sel di Excel. Format bersyarat adalah format yang diterapkan hanya pada sel yang memenuhi kriteria tertentu, katakanlah nilai di atas nilai tertentu, nilai positif atau negatif, atau nilai dengan rumus tertentu, dll. Pemformatan bersyarat ini juga dapat dilakukan dalam pemrograman VBA excel menggunakan ' Format Ketentuan Koleksi ' di makro / prosedur.

Kondisi Format digunakan untuk merepresentasikan format bersyarat yang bisa disetel dengan memanggil metode yang mengembalikan variabel jenis itu. Ini berisi semua format bersyarat untuk satu rentang dan hanya dapat menampung tiga kondisi format.

FormatConditions.Add / Modify / Delete digunakan di VBA untuk menambah / memodifikasi / menghapus objek FormatCondition ke koleksi. Setiap format diwakili oleh objek FormatCondition. FormatConditions adalah properti dari objek Range dan Add memiliki parameter berikut dengan sintaks di bawah ini:

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

Sintaks rumus Tambahkan memiliki argumen berikut:

  • Tipe: Diperlukan, menunjukkan apakah format bersyarat didasarkan pada nilai yang ada di sel atau ekspresi
  • Operator: Opsional, mewakili operator yang akan digunakan dengan nilai ketika 'Type' didasarkan pada nilai sel
  • Formula1: Opsional, mewakili nilai atau ekspresi yang terkait dengan format bersyarat.
  • Formula2: Opsional, mewakili nilai atau ekspresi yang terkait dengan bagian kedua dari format bersyarat jika parameter: 'Operator' adalah 'xlBetween' atau 'xlNotBetween'

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

Berikut adalah daftar beberapa nilai / enumerasi yang dapat diambil oleh beberapa parameter 'Add' / 'Modify':

Contoh Pemformatan Bersyarat VBA

Di bawah ini adalah contoh pemformatan bersyarat di excel vba.

Anda dapat mengunduh Templat Pemformatan Bersyarat VBA ini di sini - Templat Pemformatan Bersyarat VBA

Contoh 1

Katakanlah kita memiliki file Excel yang berisi beberapa nama dan tanda siswa, dan kita ingin menentukan / menyorot tanda tersebut sebagai Bold dan berwarna biru yang lebih besar dari 80, dan sebagai Bold dan Red dalam warna yang kurang dari 50. Mari kita lihat data yang terdapat di dalam file tersebut:

Kami menggunakan fungsi FormatConditions.Add seperti di bawah ini untuk mencapai ini:

  • Pergi ke Pengembang -> Editor Visual Basic:

  • Klik kanan pada nama workbook di panel 'Project-VBAProject' -> 'Insert' -> 'Module'.

  • Sekarang tulis kode / prosedur di modul ini:

Kode:

 Pemformatan sub () Akhir Sub

  • Tentukan variabel rng, condition1, condition2:

Kode:

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

  • Setel / perbaiki rentang pemformatan bersyarat yang diinginkan menggunakan fungsi 'Range' VBA:

Kode:

 Sub formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") End Sub

  • Hapus / hapus semua pemformatan bersyarat yang ada (jika ada) dari rentang, menggunakan 'FormatConditions.Delete':

Kode:

 Sub formatting () 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 formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete Setel condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Setel condition2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub

  • Tentukan dan setel format yang akan diterapkan untuk setiap kondisi

Salin dan tempel kode ini ke modul kelas VBA Anda.

Kode:

Sub pemformatan () 'Mendefinisikan variabel: Dim rng Sebagai Rentang Dim condition1 Sebagai FormatCondition, condition2 Sebagai 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 Setel condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80") Set condition2 = rng.FormatConditions. Tambahkan (xlCellValue, xlLess, "= 50") 'Mendefinisikan dan mengatur format yang akan diterapkan untuk setiap kondisi Dengan condition1 .Font.Color = vbBlue .Font.Bold = True End With Dengan condition2 .Font.Color = vbRed .Font. Bold = True End Dengan End Sub

Sekarang ketika 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 bisa digunakan dengan FormatCondition adalah:

Contoh # 2

Katakanlah dalam contoh di atas kita memiliki kolom lain juga yang menyatakan bahwa siswa tersebut adalah 'Topper' jika dia mendapat nilai lebih dari 80 nilai, jika tidak Lulus / Gagal ditulis melawan mereka. Sekarang kami ingin menyoroti nilai yang dinyatakan sebagai 'Topper' sebagai Bold dan Blue. Mari kita lihat data yang terdapat di dalam file tersebut:

Dalam kasus ini, kode / prosedur akan bekerja sebagai berikut:

Kode:

 Sub TextFormatting () End Sub

Tentukan dan setel format yang akan diterapkan untuk setiap kondisi

Kode:

 Sub TextFormatting() With Range("c2:c11").FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:="topper") With .Font .Bold = True .Color = vbBlue End With End With End Sub

We can see in the above code that we wish to test if the range: ‘C2:C11” contains the string: “Topper”, so the parameter: “Operator” of ‘Format.Add’ takes the enumeration:”xlContains”, to test this condition in the fixed range (i.e C2:C11), and then do the required conditional formatting (font changes) on this range.

Now when we run this code manually or by pressing the F5 key, we see that cell values with ‘Topper’ get highlighted in Blue and bold:

Note: So, we have seen in the above two examples how the ‘Add’ method works in case of any cell value criteria (numeric or text string).

Below are some other instances/criteria that can be used to test and thus apply VBA conditional formatting on:

  • Format by Time Period
  • Average condition
  • Colour Scale condition
  • IconSet condition
  • Databar condition
  • Unique Values
  • Duplicate Values
  • Top10 values
  • Percentile Condition
  • Blanks Condition, etc.

With different conditions to be tested, different values/enumeration are taken by parameters of ‘Add’.

Things to Remember About VBA Conditional Formatting

  • ‘Add’ method with ‘FormatConditions’ is used to create a new conditional format, ’Delete’ method to delete any conditional format, and ‘Modify’ method to alter any existing conditional format.
  • The ‘Add’ method with ‘FormatConditions Collection’ fails if more than three conditional formats are created for a single range.
  • To apply more than three conditional formats to a range using the ‘Add’ method, we can use ‘If’ or ‘select case’.
  • If the ‘Add’ method has its ‘Type’ parameter as: ‘xlExpression’, then the parameter ‘Operator’ is ignored.
  • The parameters: ‘Formula1’ and ‘Formula2’ in the ‘Add’ method can be a cell reference, constant value, string value, or even a formula.
  • The parameter: ‘Formula2’ is used only when the parameter: ‘Operator’ is either ‘xlBetween’ or ‘xlNotBetween’, else it is ignored.
  • To remove all the conditional formatting from any worksheet, we can use the ‘Delete’ method as follows:
Cells.FormatConditions.Delete