Làm nổi bật ô chọn với hàm bổ trợ tạo định dạng có điều kiện

[HeSanbi]

Hôm nay tôi chia sẻ với các bạn hàm tạo định dạng có điều kiện để tô màu cột và dòng làm nổi bật để đối chiếu ô đang chọn, giúp chúng ta có cái nhìn trực quan trong bảng tính Excel.

Mặc dù có thể tạo định dạng có điều kiện bằng Trình tạo định dạng có điều kiện có sẵn của Excel. Tuy nhiên để thuận tiện, làm cho việc tạo dễ dàng và nhanh chóng hơn, chỉ cần tận dụng VBA để tự động hóa chúng. Các hàm dưới đây chỉ là bổ trợ tạo Formatting conditions, chứ không phải hàm chức năng.

Ưu điểm của cách tô màu nổi bật với định dạng có điều kiện:

1. Đối chiếu dòng cột dễ dàng.
2. Định dạng không làm ảnh hướng đến chế độ Undo và Redo của Excel.
3. Dễ dàng tạo định dạng điều kiện với việc gõ hàm.

Nhược điểm:
Cách tô màu với định dạng có điều kiện gây tốn kém tài nguyên, khi thực hiện tính toán lại, nếu vùng ô quá lớn.

***Nếu dự án của bạn đã có nhiều công thức tính toán thì không nên sử dụng cách này để tô màu.

Hình ảnh làm nổi bật ô với định dạng có điều kiện:

​

​

​

Nếu thao tác tay thì các công thức Formatting Conditions để tô màu ô như dưới đây:
1. Công thức tô màu tại ô ActiveCell:

=AND(COLUMN()=CELL("COL"),CELL("COL")>=CELL("COL",table1),CELL("COL")<=(CELL("COL",table1)+COLUMNS(table1)-1),ROW()=CELL("ROW"),CELL("ROW")>=CELL("ROW",table1),CELL("ROW")<=(CELL("ROW",table1)+ROWS(table1)-1))

2. Công thức tô màu dòng:

=AND(ROW()=CELL("ROW"),CELL("COL")>=CELL("COL",table1),CELL("COL")<=(CELL("COL",table1)+COLUMNS(table1)-1),CELL("ROW")>=CELL("ROW",table1),CELL("ROW")<=(CELL("ROW",table1)+ROWS(table1)-1))

3. Công thức tô màu cột:

=AND(COLUMN()=CELL("COL"),CELL("COL")>=CELL("COL",table1),CELL("COL")<=(CELL("COL",table1)+COLUMNS(table1)-1),CELL("ROW")>=CELL("ROW",table1),CELL("ROW")<=(CELL("ROW",table1)+ROWS(table1)-1))

Đặt Name cho một vùng ô là table1 và trong cửa sổ Formatting Conditions sổ chọn điều kiện như ảnh dưới đây, nhập biểu thức và chọn màu nổi bật.

​

Kết quả sau khi nhập:

​

Hướng dẫn sử dụng hàm bổ trợ để tạo nhanh:

Sau khi tải về và cài đặt Add-in bên dưới, có thể sử dụng hướng dẫn dưới đây để thực hiện.

Hàm:
=CellHighlight(RangeEvent,[Đối_số_cài_đặt])

Gõ 1 hàm duy nhất cho 1 vùng ô cần tô màu, vào một ô bất kỳ không sử dụng đến.

Các hàm đối số cài đặt

​

Hàm đối số cài đặt	Diễn giải
HL_Column(color=0)	Nhập hàm thì tô cột với màu chỉ định hoặc màu mặc định
HL_Row(color=0)	Nhập hàm thì tô dòng với màu chỉ định hoặc màu mặc định
HL_ActiveCell(color=0, borderColor=0)	Nhập hàm thì tô ô chọn với màu chỉ định hoặc màu mặc định
CellHighlight_ShowWindow()	Nhập hàm này ô bất kì để mở Cửa sổ Formatting Conditions
CellHighlight_Delete()	Xóa điều kiện đã đặt, hàm có sẵn trong ô, thêm _Delete và nhấn Enter Hoặc nếu nhập hàm này trên vùng ô đang có định dạng.
CellHighlight_DeleteAll()	Xóa tất cả điều kiện tô màu
CellHighlight_CopyCode()	Chép mã vào bộ nhớ tạm để dán vào mã ThisWorkbook
CellHighlight_HuongDan()	Tự động tạo trang tính hướng dẫn sử dụng

Ví dụ: Tô màu vùng ô A1:Z1000 được đặt tên là Table1, với tô cột, dòng và ô chọn

=CellHighlight(Table1,HL_Row(),HL_Column(),HL_ActiveCell())

Ví dụ: Tô màu vùng ô A1:Z1000 được đặt tên là Table1, tô dòng màu #5CC8B3 và ô chọn màu mặc định

=CellHighlight(Table1,HL_Row("#5CC8B3"),HL_ActiveCell())

Ví dụ CellHighlight_Delete:
Nếu ô chứa hàm =CellHighlight(Table1,HL_ActiveCell()) để xóa chỉ cần thêm _Delete và nhấn Enter
Nếu nhập =CellHighlight_Delete() trên vùng ô có định dạng thì thực hiện xóa.

Chọn màu sắc cho định dạng màu nền:

Để đặt màu sắc có thể chọn màu trong bảng chọn màu, hoặc một số đại diện màu sắc hoặc tên màu tiếng Anh.​

​

Dưới bảng chọn màu hãy chọn màu Hệ thập lục phân, ví dụ chọn và nhập HL_Row("#5CC8B3")​

​

Nếu bạn đã tạo xong các định dạng có điều kiện cho các vùng ô, bạn có thể xóa tất cả mã trong module đi, để lại các dòng mã mà mã trong ThisWorkbook gọi.

Để sử dụng được Hàm trong dự án mới, hãy sao chép module modXLCellHighlight và mã trong ThisWorkbook.
***Lưu ý: khi sử dụng mã VBA thì dự án của bạn cần lưu ở các định dạng xlsm, xlsb hoặc dạng add-in xla, xlam.

Các bạn có thể tải Add-in, cài đặt để sử dụng lại cho các dự án khác


Hàm hỗ trợ thêm trong việc tô màu nền trực quan với Add-in XLCellHighlight xlam:

Ví dụ bạn muốn tô màu từ ô F4 cho đến F4:J25, hãy nhập màu nền cho F4 trắng và J4 cam, thường thì là màu sáng và nhập công thức sau:​

=FillColor(F4:J25)​

​

Nếu bạn muốn chọn mô hình màu, thì gồm các hàm dưới đây, với 2 tham số, vị trí màu, và khoảng cách không gian màu:​

​

Fill_HUE(Starting, Fractor)​

Fill_Natural(Starting, Fractor)​

Fill_Lightness(Starting, Fractor)​

​

Công thức nhập như sau: =FillColor(F4:J25, Fill_Natural(0, 20))​

​

Starting luôn bắt đầu từ 0, nếu bạn muốn lệch bao nhiêu thì thêm vào. Khoảng cách không gian màu tùy màu mà đối số có thể âm.​

Các hàm này phải được nhập trong hàm FillColor.​

​

Sau khi tô xong bạn có thể xóa hàm đã gõ đi.​

​

Hình ảnh kết quả​

​

​

Các bạn có thể tham khảo thêm Add-in làm nổi bật ô chọn:

LocalizeXL v1.71 - Làm nổi bật ô đang chọn và cuộn trang tự động

***** CẬP NHẬT v1.71 MỚI NHẤT 14/07/2023***** Chia sẻ ứng dụng làm nổi bật vùng ô Excel đang chọn. #LocalizeXL Add-in Mục đích của ứng dụng này được sinh ra là để ta cho cái nhìn trực quan hơn khi ta chọn một ô Excel, hoặc ta có thể biết được vùng ô trước đó đã chọn, ta cũng có thể dùng để đối...

giaiphapexcel.com

Các bài viết của tôi tại tag #sanbi vba


(Để tải được tệp dưới cần đăng nhập)
***Mật khẩu VBA là 1

 

[VetMini]

...
Đề nghị các bạn đọc cho kỹ để biết rằng tôi không góp ý nặng nề, cũng không đổ lỗi.
Còn ý chính của tôi là gì, cũng cảm phiền đọc lại từ đầu. Và làm ơn đọc kỹ. Tôi cũng đã nói từ bài 15 rằng tôi dừng.

View attachment 302776

Xin nói trước là tôi không muốn dính dáng đến code kiếc bài này. Những gì liếc qua thấy nó không đúng tôi cũng không muốn dây dưa. Chỉ khi nào thấy cái sai ảnh hưởng đến kiến thức các bạn ham học code tôi mới nói.

Những dòng sau đây là tôi nói cho người tôi quote ở trên:
Thường thì người ta sửa code sẽ có chỗ cho biết phiên bản, và một bảng lịch sử (history), Có thể nằm trong code, hoặc file gì đó cho biết phiên bản cải tiến như thế nào? chỉnh sửa lỗi, chỉnh giao diện (*), tăng hiêu quả, vv...?

(*) Là dân code HĐT, giao diện đối với tôi là quan trọng nhất. Thường thường chỉnh sửa giao diện có nghĩa là thay đổi những trường hợp mà phiên bản trước không hổ trợ.

 

[ptm0412]

Những dòng sau đây là tôi nói cho người tôi quote ở trên:
Thường thì người ta sửa code sẽ có chỗ cho biết phiên bản, và một bảng lịch sử (history), Có thể nằm trong code, hoặc file gì đó cho biết phiên bản cải tiến như thế nào? chỉnh sửa lỗi, chỉnh giao diện (*), tăng hiêu quả, vv...?

Tôi cũng nói trước là tôi chỉ trả lời người tôi trích dẫn:
- Thứ nhất, tôi biết rằng code viết xong sẽ phải có lỗi. Dù đã tự test bao nhiêu cũng sẽ vẫn còn. Đưa lên cộng đồng là việc tốt, tốt cho cả hai phía.
- Thứ hai, tôi không tải về, tôi không thử. Nhưng tôi thấy bài 2 báo lỗi, bài 3 nhận là lỗi và xác định đã sửa lỗi (xem minh họa). Thế nên tôi mới có ý kiến, mà ý kiến của tôi cũng không nói lỗi của ai, không chê rằng lỗi người viết code hay xác định lỗi của MS.
- Thứ ba, nếu tất cả là do phiên bản Office gây ra, không phải lỗi, thì cũng chưa phải là hoàn thiện. Huống hồ không phải tất cả là lỗi do MS (chẳng hạn như "Ctrl Enter gây rối", "gõ công thức sai chỗ"). Đó không phải lỗi viết code thật, nhưng đó là chưa lường trước lỗi do người dùng gây ra.

 

[test1986]

Em đọc qua code của bác @HeSanbi thì không hiểu được phần code của Function StandardColor()

Private Function StandardColor(ByVal color As String) As Long
Dim v As Long
Select Case color
Case "yellow", "ye", "yl": v = vbYellow
Case "red", "re": v = vbRed
Case "blue": v = vbBlue
Case "green", "gr": v = vbGreen
Case "cyan", "cy": v = vbCyan
Case "magenta", "ma": v = vbMagenta
Case "white", "wh", "wi": v = vbWhite
Case "black", "bl", "bk": v = vbBlack
Case "orange", "or": v = &H71AFFF
Case "pink": v = &HE819E8
Case "purple", "pu": v = &HB44343
Case "silver", "si": v = &HCBCBCB
Case "violet", "vi": v = &HF5A2BF
Case "Brown", "br": v = &H3232AA
Case "Beige", "be": v = &HE819E8
Case Else
Select Case True
Case color Like "*[a-fA-F]*"
If color Like "[#]*" Then color = Mid(color, 2)
color = Mid(color, 5, 2) & Mid(color, 3, 2) & Mid(color, 1, 2)
v = CLng(IIf(color Like "&H*", "", "&H") & color)
Case IsNumeric(color): v = CLng(color)
Case Else: v = -1
End Select
End Select
StandardColor = v
End Function

Em đang thắc mắc như sau:
1/ Hệ màu của function này là hệ màu gì vì sử dụng &H ở đầu chuỗi?
2/ Cách chuyển đổi hệ màu từ RGB về hệ màu của Function này với ạ?

Em cũng đang tìm hiểu nên còn nhiều thắc mắc cơ bản, mong mọi người đừng trách em ạ. Em xin cảm ơn ạ.

 

[HeSanbi]

@test1986 Sử dụng chuẩn tên màu Hexadecimal bạn nhé
Gồm # và giá trị Hex, tức là: #FFFFFF, nhập là StandardColor("#FFFFFF").
Nếu bạn vô tình nhập &H và giá trị Hex, tức là: &HFFFFFF, nhập là StandardColor("&HFFFFFF"), định nghĩa cú pháp này chỉ có trong VB.
Thì vẫn hợp lệ.

 

[test1986]

@test1986 Sử dụng chuẩn tên màu Hexadecimal bạn nhé
Gồm # và giá trị Hex, tức là: #FFFFFF, nhập là StandardColor("#FFFFFF").
Nếu bạn vô tình nhập &H và giá trị Hex, tức là: &HFFFFFF, nhập là StandardColor("&HFFFFFF"), định nghĩa cú pháp này chỉ có trong VB.
Thì vẫn hợp lệ.

Dạ vì em sử dụng màu #E9967A

DarkSalmon

E9967A

233, 150, 122

với đoạn code như sau:

Const cl1& = &HE9967A
Const cl2& = &HE9967A
Const cl3& = &HE9967A

nhưng kết quả trả về lại không giống với màu DarkSalmon lắm anh @HeSanbi .

 

[HeSanbi]

Dạ vì em sử dụng màu #E9967A

DarkSalmon

E9967A

233, 150, 122

với đoạn code như sau:

nhưng kết quả trả về lại không giống với màu DarkSalmon lắm anh @HeSanbi .

Đảo như sau bạn nhé: E9 96 7A
Thành: 7A 96 E9
Vị trí cặp ký tự 1 sang 3 và 3 sang 1
Hoặc là sửa mã hàm StandardColor, trong hàm tôi đã đảo vị trí này, nếu số HEX được chép từ các chương trình hút màu.