VBA if a row is all blank in a range of columns, highlight the blanks and the headers

[AnnAnn]

Hello,
This code works but takes 11 seconds to run on a 500 row sheet; it's part of a large main macro and I need it to run faster. I've searched Google, YouTube, and other threads on this site but can't find what I need.
Dim lastRow As Long, lastCol As Long, i As Long
Dim col As Variant
Dim headerCell As Range

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

Dim checkColumns As Variant
checkColumns = Array("A", "B", "C", "D", "E", "F", "G", "H", "K", "M", "P", "Q", "S", "U", "W", "Y", "AA")

For i = 3 To lastRow
Dim allBlank As Boolean

allBlank = True


For Each col In checkColumns
If ws.Cells(i, col).Value <> "" Then
allBlank = False
Exit For
End If
Next col

If allBlank Then

For Each col In checkColumns
ws.Cells(i, col).Interior.Color = RGB(255, 204, 0)
Set headerCell = ws.Cells(2, col)
headerCell.Interior.Color = RGB(0, 0, 0)
headerCell.Font.Color = RGB(255, 255, 255)
Next col
End If

Next i

 

[mumps]

In this case you would want rows 2, 3, 4, 5, 8, 10, 12 and 13 to be highlighted. If that is correct, remove the "ws" variable and try my macro. It should work.

 

[AnnAnn]

In this case you would want rows 2, 3, 4, 5, 8, 10, 12 and 13 to be highlighted. If that is correct, remove the "ws" variable and try my macro. It should work.

I need the "ws" variables because my data is in a different workbook. When I remove them, the code runs on the macro workbook instead of the workbook with the data, please see screenshot.

 

[mumps]

I just wanted you to test the macro independently to see if it works on your data. Put the macro (without the ws) in the workbook containing the data. Make the sheet with the data the active sheet and run the macro. If it works, then the problem is not with the macro I suggested, but with something that the other macros being called are doing.

 

[AnnAnn]

I just wanted you to test the macro independently to see if it works on your data. Put the macro (without the ws) in the workbook containing the data. Make the sheet with the data the active sheet and run the macro. If it works, then the problem is not with the macro I suggested, but with something that the other macros being called are doing.

I have commented out all the other subs on the main macro, this one is the only sub that is running on the macro workbook.
I did as you suggested and added the macro to the data workbook and it does not function as needed; it runs but it highlights all the columns, please see screenshot.

 

[mumps]

Try this version:

Sub ColorCells()
    Dim lRow As Long, i As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = 3 To lRow
        If WorksheetFunction.CountA(Intersect(Rows(i), Range("A:H,K:K,M:M,P:Q,S:S,U:U,W:W,Y:Y,AA:AA"))) = 0 Then
            Intersect(Rows(i), Range("A:H,K:K,M:M,P:Q,S:S,U:U,W:W,Y:Y,AA:AA")).Interior.Color = RGB(255, 204, 0)
            Intersect(Rows(2), Range("A:H,K:K,M:M,P:Q,S:S,U:U,W:W,Y:Y,AA:AA")).Interior.Color = RGB(0, 0, 0)
            Intersect(Rows(2), Range("A:H,K:K,M:M,P:Q,S:S,U:U,W:W,Y:Y,AA:AA")).Font.Color = RGB(255, 255, 255)
        End If
    Next i
End Sub

 

[AnnAnn]

Try this version:

Sub ColorCells()
    Dim lRow As Long, i As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = 3 To lRow
        If WorksheetFunction.CountA(Intersect(Rows(i), Range("A:H,K:K,M:M,P:Q,S:S,U:U,W:W,Y:Y,AA:AA"))) = 0 Then
            Intersect(Rows(i), Range("A:H,K:K,M:M,P:Q,S:S,U:U,W:W,Y:Y,AA:AA")).Interior.Color = RGB(255, 204, 0)
            Intersect(Rows(2), Range("A:H,K:K,M:M,P:Q,S:S,U:U,W:W,Y:Y,AA:AA")).Interior.Color = RGB(0, 0, 0)
            Intersect(Rows(2), Range("A:H,K:K,M:M,P:Q,S:S,U:U,W:W,Y:Y,AA:AA")).Font.Color = RGB(255, 255, 255)
        End If
    Next i
End Sub

I received runtime error 1004 at: If WorksheetFunction.CountA(Intersect(ws.Rows(i), ws.Range("A:H,K:K,M:M,P:Q,S:S,U:U,W:W,Y:Y,AA:AA"))) = 0 Then

 

[mumps]

This is what I got when I reproduced your sample data:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2	a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p	q	r	s	t	u	v	w	x	y	z	aa
3
4
5
6																	1234566
7		1234566				1234566
8
9				1234566
10
11							1234566
12
13
Sheet1

 

[AnnAnn]

This is what I got when I reproduced your sample data:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2	a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p	q	r	s	t	u	v	w	x	y	z	aa
3
4
5
6																	1234566
7		1234566				1234566
8
9				1234566
10
11							1234566
12
13
Sheet1

I believe that is because you ran the code on the data workbook. All of my subs have to have "ws" in front of Cells, Rows, and Range, otherwise the macro will run on the macro workbook instead of the workbook the data is on. Please see the screenshot in #22, if needed.

 

[mumps]

If it works independently as I have shown, since I don’t have access to your workbooks, you will have to play with the code to make it work for you if possible.

 

[AnnAnn]

If it works independently as I have shown, since I don’t have access to your workbooks, you will have to play with the code to make it work for you if possible.

I don't understand what I need to do to play with the code to make it work using "ws". I always receive runtime error 1004 when I use Union or Intersect and I have searched Google, YouTube, and the Stack Overflow forums and this forum and cannot find an answer. Thanks anyway.