Định dạng có điều kiện VBA - Áp dụng định dạng có điều kiện bằng VBA Excel

Định dạng có điều kiện trong Excel VBA

Chúng ta có thể áp dụng định dạng có điều kiện cho một ô hoặc nhiều ô trong Excel. Định dạng có điều kiện là định dạng chỉ được áp dụng cho các ô đáp ứng các tiêu chí nhất định, chẳng hạn như giá trị trên một giá trị cụ thể, giá trị dương hoặc âm hoặc giá trị với một công thức cụ thể, v.v. Định dạng có điều kiện này cũng có thể được thực hiện trong lập trình VBA excel bằng cách sử dụng ' Tập hợp Điều kiện Định dạng ' trong macro / thủ tục.

Định dạng Điều kiện được sử dụng để biểu thị một định dạng có điều kiện có thể được đặt bằng cách gọi một phương thức trả về một biến của kiểu đó. Nó chứa tất cả các định dạng có điều kiện cho một phạm vi duy nhất và chỉ có thể chứa ba điều kiện định dạng.

FormatConditions.Add / Modify / Delete được sử dụng trong VBA để thêm / sửa đổi / xóa các đối tượng FormatCondition vào bộ sưu tập. Mỗi định dạng được đại diện bởi một đối tượng FormatCondition. FormatConditions là một thuộc tính của đối tượng Range và Thêm các tham số sau với cú pháp dưới đây:

FormatConditions.Add (Loại, Toán tử, Công thức 1, Công thức 2)

Cú pháp công thức Thêm có các đối số sau:

  • Loại: Bắt buộc, biểu thị nếu định dạng có điều kiện dựa trên giá trị có trong ô hoặc một biểu thức.
  • Toán tử: Tùy chọn, đại diện cho toán tử sẽ được sử dụng với một giá trị khi 'Loại' dựa trên giá trị ô.
  • Công thức 1: Tùy chọn, đại diện cho giá trị hoặc biểu thức được liên kết với định dạng có điều kiện.
  • Công thức 2: Tùy chọn, đại diện cho giá trị hoặc biểu thức được liên kết với phần thứ hai của định dạng có điều kiện khi tham số: 'Toán tử' là 'xlBetween' hoặc 'xlNotBetween.'

FormatConditions.Modify cũng có cú pháp tương tự như FormatConditions.Add.

Sau đây là danh sách một số giá trị / kiểu liệt kê có thể được thực hiện bởi một số tham số của 'Thêm' / 'Sửa đổi':

Ví dụ về định dạng có điều kiện VBA

Dưới đây là các ví dụ về Định dạng có điều kiện trong VBA excel.

Ví dụ 1

Giả sử chúng tôi có một tệp Excel chứa tên và điểm của một số học sinh và chúng tôi muốn xác định / đánh dấu các điểm là Đậm và màu xanh lam, lớn hơn 80 và là Đậm và Đỏ, nhỏ hơn 50. Hãy cho chúng tôi xem dữ liệu có trong tệp:

Chúng tôi sử dụng FormatConditions.thêm chức năng như bên dưới để thực hiện điều này:

  • Đi tới Nhà phát triển -> Visual Basic Editor:
  • Bấm chuột phải vào tên sổ làm việc trong ngăn 'Dự án-VBAProject'->' Chèn '->' Mô-đun. '
  • Bây giờ viết mã / thủ tục trong mô-đun này:

Mã:

Định dạng phụ () Kết thúc Sub
  • Xác định biến rng, condition1, condition2:

Mã:

Định dạng phụ () Làm mờ rng Theo phạm vi Điều kiện làm mờ 1 Như định dạng Điều kiện, điều kiện2 Như định dạng Điều kiện kết thúc Phụ
  • Đặt / sửa phạm vi mà định dạng có điều kiện được mong muốn bằng cách sử dụng hàm VBA 'Phạm vi':

Mã:

Định dạng phụ () Làm mờ rng Theo phạm vi Điều kiện làm mờ 1 Theo định dạng Điều kiện, điều kiện2 Như Định dạng Điều kiện Đặt rng = Phạm vi ("B2", "B11") Kết thúc Phụ
  • Xóa / xóa mọi định dạng có điều kiện hiện có (nếu có) khỏi phạm vi, sử dụng 'FormatConditions.Delete':

Mã:

Định dạng phụ () Dim rng As Range Dim điều kiện1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete End Sub
  • Bây giờ hãy xác định và đặt tiêu chí cho từng định dạng có điều kiện, sử dụng 'FormatConditions.Add':

Mã:

Định dạng phụ () Dim rng As Range Dim điều kiện1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete Set condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Đặt condition2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub
  • Xác định và đặt định dạng được áp dụng cho từng điều kiện

Sao chép và dán mã này vào mô-đun lớp VBA của bạn.

Mã:

Định dạng phụ () 'Định nghĩa các biến: Dim rng As Range Dim điều kiện1 As FormatCondition, condition2 As FormatCondition' Sửa / Đặt phạm vi mong muốn định dạng có điều kiện Đặt rng = Range ("B2", "B11") 'Thành xóa / xóa mọi định dạng có điều kiện hiện có khỏi phạm vi rng.FormatConditions.Delete 'Xác định và đặt tiêu chí cho từng định dạng có điều kiện Đặt điều kiện1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80") Đặt điều kiện2 = rng.FormatConditions. Add (xlCellValue, xlLess, "= 50") 'Xác định và thiết lập định dạng được áp dụng cho từng điều kiện With condition1 .Font.Color = vbBlue .Font.Bold = True End With condition2 .Font.Color = vbRed .Font. Bold = True End With End Sub

Bây giờ khi chúng tôi chạy mã này bằng phím F5 hoặc theo cách thủ công, chúng tôi thấy rằng các điểm nhỏ hơn 50 sẽ được đánh dấu bằng màu đậm và màu đỏ, trong khi các điểm lớn hơn 80 được đánh dấu bằng màu đậm và màu xanh lam như sau:

Lưu ý: Một số thuộc tính cho sự xuất hiện của các ô được định dạng có thể được sử dụng với FormatCondition là:

Ví dụ số 2

Giả sử trong ví dụ trên, chúng ta có một cột khác cũng nói rằng học sinh là 'Người đạt điểm cao' nếu em ấy đạt hơn 80 điểm, nếu không đạt / không đạt được ghi ngược lại. Bây giờ chúng tôi muốn làm nổi bật các giá trị được nêu là 'Topper' là Bold và Blue. Hãy cho chúng tôi xem dữ liệu có trong tệp:

Trong trường hợp này, mã / thủ tục sẽ hoạt động như sau:

Mã:

Sub TextFormatting () End Sub

Xác định và đặt định dạng được áp dụng cho từng điều kiện

Mã:

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

Trong đoạn mã trên, chúng ta có thể thấy rằng chúng ta muốn kiểm tra xem phạm vi: 'C2: C11' có chứa chuỗi: "Topper" hay không, vì vậy, tham số: "Onamestor" của 'Format.Add' có kiểu liệt kê: "Xcontains" thành kiểm tra điều kiện này trong phạm vi cố định (tức là, C2: C11), rồi thực hiện định dạng có điều kiện bắt buộc (thay đổi phông chữ) trên phạm vi này.

Bây giờ khi chúng tôi chạy mã này theo cách thủ công hoặc bằng cách nhấn phím F5, chúng tôi thấy rằng các giá trị ô có 'Topper' được đánh dấu bằng màu Xanh lam và in đậm:

Lưu ý: Vì vậy, chúng ta đã thấy trong hai ví dụ trên về cách phương thức 'Thêm' hoạt động trong trường hợp có bất kỳ tiêu chí giá trị ô nào (chuỗi số hoặc chuỗi văn bản).

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.
  • Để áp dụng nhiều hơn ba định dạng có điều kiện cho một dải ô bằng phương pháp 'Thêm', chúng tôi có thể sử dụng 'Nếu' hoặc 'chọn trường hợp.'
  • Nếu phương thức 'Add' có tham số 'Type' là: 'xlExpression', thì tham số 'Operator' sẽ bị bỏ qua.
  • Các tham số: 'Formula1' và 'Formula2' trong phương thức 'Thêm' có thể là tham chiếu ô, giá trị hằng số, giá trị chuỗi hoặc thậm chí là công thức.
  • Tham số: 'Formula2' chỉ được sử dụng khi tham số: 'Operator' là 'xlBetween' hoặc 'xlNotBetween, nếu không, nó sẽ bị bỏ qua.
  • Để xóa tất cả định dạng có điều kiện khỏi bất kỳ trang tính nào, chúng tôi có thể sử dụng phương pháp 'Xóa' như sau:
Cells.FormatConditions.Delete

thú vị bài viết...