Pemformatan Bersyarat VBA - Terapkan Format Bersyarat menggunakan VBA Excel

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 mewakili format bersyarat yang dapat 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 Tambahkan parameter berikut dengan sintaks di bawah ini:

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

Sintaks rumus Tambahkan memiliki argumen berikut:

  • Jenis: Wajib, 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.

Contoh 1

Katakanlah kita memiliki file Excel yang berisi beberapa nama dan nilai siswa, dan kami ingin menentukan / menyorot tanda tersebut sebagai Berwarna Tebal dan biru, yang lebih besar dari 80, dan sebagai Berwarna Tebal dan Merah, yang kurang dari 50. Mari kita lihat data yang terdapat pada file:

Kami menggunakan FormatConditions. Tambahkan fungsi 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 kisaran 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 Set condition1 = rng.FormatConditions.Add(xlCellValue, xlGreater, "=80") Set condition2 = rng.FormatConditions.Add(xlCellValue, xlLess, "=50") End Sub
  • Define and set the format to be applied for each condition

Copy and paste this code into your VBA class module.

Code:

Sub formatting() 'Definining the variables: Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition 'Fixing/Setting the range on which conditional formatting is to be desired Set rng = Range("B2", "B11") 'To delete/clear any existing conditional formatting from the range rng.FormatConditions.Delete 'Defining and setting the criteria for each conditional format Set condition1 = rng.FormatConditions.Add(xlCellValue, xlGreater, "=80") Set condition2 = rng.FormatConditions.Add(xlCellValue, xlLess, "=50") 'Defining and setting the format to be applied for each condition With condition1 .Font.Color = vbBlue .Font.Bold = True End With With condition2 .Font.Color = vbRed .Font.Bold = True End With End Sub

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

Catatan: Beberapa properti untuk tampilan sel yang diformat yang bisa digunakan dengan FormatCondition adalah:

Contoh # 2

Katakanlah dalam contoh di atas kita juga memiliki kolom lain yang menyatakan bahwa siswa tersebut adalah 'Topper' jika dia mendapat nilai lebih dari 80 nilai, jika tidak Lulus / Gagal menuliskannya. 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") Dengan .Font .Bold = True .Color = vbBlue End With End With End Sub

Kita dapat melihat pada kode di atas yang ingin kita uji jika range: 'C2: C11 "berisi string:" Topper ", sehingga parameter:" Onamestor "dari' Format.Add 'melakukan pencacahan:" Xcontains "menjadi uji kondisi ini dalam rentang tetap (yaitu, C2: C11), lalu lakukan pemformatan bersyarat yang diperlukan (perubahan font) 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: Jadi, kita telah melihat dalam dua contoh di atas bagaimana metode 'Tambah' bekerja jika ada kriteria nilai sel (numerik atau string teks).

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.
  • Untuk menerapkan lebih dari tiga format bersyarat ke rentang menggunakan metode 'Tambah', kita dapat menggunakan 'Jika' atau 'pilih kasus.'
  • Jika metode 'Add' memiliki parameter 'Type' sebagai: 'xlExpression', maka parameter 'Operator' diabaikan.
  • Parameter: 'Formula1' dan 'Formula2' dalam metode 'Add' dapat berupa referensi sel, nilai konstanta, nilai string, atau bahkan rumus.
  • Parameter: 'Formula2' hanya digunakan jika parameter: 'Operator' adalah 'xlBetween' atau 'xlNotBetween', jika tidak maka akan diabaikan.
  • Untuk menghapus semua pemformatan bersyarat dari lembar kerja mana pun, kita dapat menggunakan metode 'Hapus' sebagai berikut:
Cells.FormatConditions.Delete

Artikel yang menarik...