VBA highlight blank cells in a range and their corresponding headers

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.
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
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(...
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
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
Thanks, will do.
 
Upvote 0

Forum statistics

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

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