VBA highlight blank cells in a range and their corresponding headers

[AnnAnn]

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

 

[Joe4]

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:

Application.WorksheetFunction.CountBlank(...

 

[AnnAnn]

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:

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.

 

[AnnAnn]

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

 

[Joe4]

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?

 

[AnnAnn]

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?

A friend gave it to me.

 

[Joe4]

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

 

[AnnAnn]

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.