Pemformatan Bersyarat VBA

Pemformatan Bersyarat dalam Excel VBA

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

Format Condition digunakan untuk mewakili format bersyarat yang dapat ditetapkan dengan memanggil kaedah yang mengembalikan pemboleh ubah dari jenis itu. Ia mengandungi semua format bersyarat untuk julat tunggal dan hanya boleh memuatkan tiga syarat format.

FormatConditions.Add / Modify / Delete digunakan dalam VBA untuk menambah / mengubah / menghapus objek FormatCondition ke koleksi. Setiap format diwakili oleh objek FormatCondition. FormatConditions adalah harta benda Range dan Add mempunyai parameter berikut dengan sintaks di bawah:

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

Sintaks formula tambah mempunyai argumen berikut:

  • Jenis: Wajib, mewakili jika format bersyarat berdasarkan nilai yang ada dalam sel atau ekspresi
  • Operator: Pilihan, mewakili operator yang akan digunakan dengan nilai ketika 'Type' berdasarkan nilai sel
  • Formula1: Pilihan, mewakili nilai atau ungkapan yang berkaitan dengan format bersyarat.
  • Formula2: Pilihan, mewakili nilai atau ungkapan yang dikaitkan dengan bahagian kedua dari format bersyarat apabila parameter: 'Operator' sama ada 'xlBetween' atau 'xlNotBetween'

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

Berikut adalah daftar beberapa nilai / penghitungan yang dapat diambil oleh beberapa parameter 'Tambah' / 'Ubah suai':

Contoh Pemformatan Bersyarat VBA

Berikut adalah contoh pemformatan Bersyarat di excel vba.

Anda boleh memuat turun Templat Pemformatan Bersyarat VBA ini di sini - Templat Pemformatan Bersyarat VBA

Contoh # 1

Katakan kita mempunyai fail Excel yang mengandungi beberapa nama dan tanda pelajar, dan kita ingin menentukan / menonjolkan tanda sebagai Bold dan warna biru yang lebih besar daripada 80, dan sebagai warna Bold dan Red yang kurang dari 50. Mari kita lihat data yang terdapat dalam fail:

Kami menggunakan fungsi FormatConditions.Add seperti di bawah untuk menyelesaikannya:

  • Pergi ke Pembangun -> Editor Visual Basic:

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

  • Sekarang tulis kod / prosedur dalam modul ini:

Kod:

 Sub format () End Sub

  • Tentukan pemboleh ubah rng, condition1, condition2:

Kod:

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

  • Tetapkan / perbaiki julat pemformatan bersyarat yang diinginkan menggunakan fungsi 'Range' VBA:

Kod:

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

  • Padam / hapus sebarang format bersyarat yang ada (jika ada) dari julat, menggunakan 'FormatConditions.Delete':

Kod:

 Sub format () Dim rng Sebagai Range Dim condition1 Sebagai FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Deteete End Sub

  • Sekarang tentukan dan tetapkan kriteria untuk setiap format bersyarat, menggunakan 'FormatConditions.Add':

Kod:

 Sub format () Dim rng Sebagai Range Dim condition1 Sebagai FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Deteete Set condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Tetapkan syarat2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") Sub Akhir

  • Tentukan dan tetapkan format yang akan digunakan untuk setiap keadaan

Salin dan tampal kod ini ke dalam modul kelas VBA anda.

Kod:

Sub format () 'Mendefinisikan pemboleh ubah: Dim rng Sebagai Range Dim keadaan1 Sebagai FormatCondition, condition2 As FormatCondition' Memperbaiki / Menetapkan julat yang mana format bersyarat yang diinginkan Tetapkan rng = Range ("B2", "B11") 'To hapus / hapus sebarang format bersyarat yang ada dari julat rng.FormatConditions.Delete 'Menentukan dan menetapkan kriteria untuk setiap format bersyarat Tetapkan syarat1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80") Tetapkan syarat2 = rng.FormatConditions Tambahkan (xlCellValue, xlLess, "= 50") 'Mendefinisikan dan menetapkan format yang akan digunakan untuk setiap keadaan Dengan syarat1 .Font.Color = vbBlue .Font.Bold = True End With With condition2 .Font.Color = vbRed .Font. Tebal = Hujung Benar Dengan Sub Akhir

Sekarang apabila kita menjalankan kod ini menggunakan kekunci F5 atau secara manual, kita melihat bahawa tanda yang kurang dari 50 akan diserlahkan dengan tebal dan merah, sementara tanda yang lebih besar daripada 80 akan diserlahkan dengan huruf tebal dan biru seperti berikut:

Catatan: Beberapa sifat untuk penampilan sel berformat yang dapat digunakan dengan FormatCondition adalah:

Contoh # 2

Katakan dalam contoh di atas kita mempunyai ruangan lain yang menyatakan bahawa pelajar itu adalah 'Topper' jika dia mendapat markah lebih daripada 80 markah, jika tidak Lulus / Gagal ditulis menentang mereka. Sekarang kami ingin menonjolkan nilai-nilai yang dinyatakan sebagai 'Topper' sebagai Bold dan Blue. Mari kita lihat data yang terdapat dalam fail:

Dalam kes ini, kod / prosedur akan berfungsi seperti berikut:

Kod:

 SubFormatting Teks () Sub Akhir

Tentukan dan tetapkan format yang akan digunakan untuk setiap keadaan

Kod:

 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