Pemformatan Bersyarat VBA - Terapkan Format Bersyarat menggunakan VBA Excel

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 Format Syarat ' 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 sifat objek Range, dan Tambahkan 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 ungkapan.
  • 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 berkaitan 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.

Contoh # 1

Katakanlah kami mempunyai fail Excel yang mengandungi nama dan markah beberapa pelajar, dan kami ingin menentukan / menonjolkan tanda sebagai Berwarna tebal dan biru, lebih besar daripada 80, dan berwarna Bold dan Merah, yang kurang dari 50. Mari kita lihat data yang terdapat dalam fail:

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

  • Pergi ke Pembangun -> Penyunting 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 dikehendaki 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
  • Hapus / 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 'Mendefinisikan dan menetapkan kriteria untuk setiap format bersyarat Tetapkan syarat1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80") Tetapkan syarat2 = rng.FormatConditions. Tambah (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 pada contoh di atas kita mempunyai ruangan lain juga yang menyatakan bahawa pelajar tersebut 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:

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

Kita dapat melihat dalam kod di atas yang ingin kita uji apakah rentang: 'C2: C11 "berisi string:" Topper, "jadi parameter:" Onamestor "dari' Format.Add 'mengambil penghitungan:" Xcontains "ke uji keadaan ini dalam julat tetap (iaitu, C2: C11), dan kemudian lakukan pemformatan bersyarat yang diperlukan (perubahan fon) pada julat ini.

Sekarang apabila kita menjalankan kod ini secara manual atau dengan menekan kekunci F5, kita melihat bahawa nilai sel dengan 'Topper' diserlahkan dengan warna Biru dan tebal:

Catatan: Oleh itu, kita telah melihat dalam dua contoh di atas bagaimana kaedah 'Tambah' berfungsi sekiranya terdapat kriteria nilai sel (rentetan angka atau 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 kaedah 'Tambah', kita dapat menggunakan 'Jika' atau 'pilih huruf besar.'
  • Jika kaedah 'Tambah' memiliki parameter 'Type' sebagai: 'xlExpression', maka parameter 'Operator' akan diabaikan.
  • Parameter: 'Formula1' dan 'Formula2' dalam kaedah 'Tambah' boleh menjadi rujukan sel, nilai tetap, nilai rentetan, atau bahkan formula.
  • Parameter: 'Formula2' hanya digunakan apabila parameter: 'Operator' sama ada 'xlBetween' atau 'xlNotBetween,' jika tidak, ia diabaikan.
  • Untuk membuang semua pemformatan bersyarat dari mana-mana lembaran kerja, kita dapat menggunakan kaedah 'Hapus' seperti berikut:
Sel.FormatKondisi. Padam

Artikel menarik...