VBA highlight blank cells in a range and their corresponding headers

A

AnnAnn

New Member
Joined
Mar 26, 2024
Messages
32
Office Version
  1. 2016
Hello,
I have the following code, which runs in 2 seconds, which is great. But, it highlights all the header columns even if a column does not contain any blank cells.
How do I change the code so that the header is only highlighted if a cell in a column is highlighted? Everything I've tried makes the Sub run for at least 10 seconds, which isn't ideal since this is a part of a main macro.
Goal: Highlight all blank cells in the referenced columns; if a cell in one of those columns is highlighted, highlight the corresponding column header. Example: There are blank cells highlighted in columns DF, DH, and DI, only those column headers should be highlighted.
Dim rCell As Range, rRange As Range
Dim col As Variant
Dim lastCol As Long, lastRow As Long

lastCol = last_col(ws)
lastRow = Last_Row_For_Realsies(ws, lastCol)

Set rRange = ws.Range("DF3:DS" & lastRow)

rRange.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 204, 0)

Dim checkColumns As Variant
checkColumns = Array("DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS")


For Each col In checkColumns

If rRange.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 204, 0) Then


With ws.Range(col & 2)
.Interior.Color = RGB(0, 0, 0)
.Font.Color = RGB(255, 255, 255)
End With
End If

Next col
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Welcome to the Board!

So, it is just highlighting blank cells in the column?
If so, why not just use the COUNTBLANK to count the number of blank cells in each column, and if it is greater than 0, then highlight your column header?

You can use the COUNTBLANK function in VBA by referencing it like this:
VBA Code: 
Application.WorksheetFunction.CountBlank(...
 
Upvote 0
Joe4 said:
Welcome to the Board!

So, it is just highlighting blank cells in the column?
If so, why not just use the COUNTBLANK to count the number of blank cells in each column, and if it is greater than 0, then highlight your column header?

You can use the COUNTBLANK function in VBA by referencing it like this:
VBA Code: 
Application.WorksheetFunction.CountBlank(...
Click to expand...
Thank you!
I am very new to VBA and am not sure how to write the code you referenced for each column. Can you give me an example using the columns referenced in my question please? Also, the code needs to be as fast as possible and I'm concerned if I do the COUNTBLANK for each column that it is going to greatly increase the run time.
 
Upvote 0
Thank you for replying! I ended up getting it to work with this code, that still only took 2 seconds to run on a 500 row sheet.

Dim checkColumns As Variant
Dim col As Variant
Dim lastCol As Long, lastRow As Long

Dim rng As Range
Dim headerCell As Range


Dim blank_cnt As Long

lastCol = last_col(ws)
lastRow = Last_Row_For_Realsies(ws, lastCol)



checkColumns = Array("DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS")

For Each col In checkColumns
Set rng = ws.Range(col & "3:" & col & lastRow)
Set headerCell = ws.Cells(2, col)
blank_cnt = 0

On Error Resume Next
blank_cnt = rng.SpecialCells(xlCellTypeBlanks).Count
On Error GoTo 0

If blank_cnt > 0 Then
rng.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 204, 0)
headerCell.Interior.Color = RGB(0, 0, 0)
headerCell.Font.Color = RGB(255, 255, 255)
End If


Next col
 
Upvote 0
I am glad you figured it out.

I am a bit curious, being that you are new to VBA, how did you come up with this code?
 
Upvote 0
OK, just please be aware that if you do post the same questions that you post here on other forums too (known as "Cross Posting"), it is fine to do so, as long as you follow the rules mentioned in rule here (which essentially means just mention that you are doing so, and provide links to those other questions): Message Board Rules

Thanks
 
Upvote 0
Joe4 said:
OK, just please be aware that if you do post the same questions that you post here on other forums too (known as "Cross Posting"), it is fine to do so, as long as you follow the rules mentioned in rule here (which essentially means just mention that you are doing so, and provide links to those other questions): Message Board Rules

Thanks
Click to expand...
Thanks, will do.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA if a row is all blank in a range of columns, highlight the blanks and the headers
2 3 4
Replies
31
Views
786
AnnAnn
A
A
  • Question
VBA Highlight Cell Based on Another Cell
Replies
1
Views
149
AnnAnn
A
A
  • Question
VBA Highlight cell if contains specific text
Replies
4
Views
353
DanteAmor
DanteAmor
A
  • Solved
Excel VBA Phone Number Format Issue
Replies
4
Views
193
AnnAnn
A
E
  • Solved
VBA -highlighting rows when a condition is met
Replies
2
Views
301
Evie76
E

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top