Color range of blank rows gray in two sheets

Question

I need to color blank rows gray, after the top blank row, in two sheets of an Excel workbook.

The sheet names are "Topic 0001" & "Topic 0002".

I get

Compile error: Block if without End if

Sub Test1()
Sheets("Topic 0001").Select
Dim rng As Range

On Error Resume Next
Set rng = Range("A1:G").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
    rng.Interior.ColorIndex = 15
End Sub

Answer 1

• Range("A1:G") has syntax error. The last data row number is missing.

---

• Highligh blank rows (all cells are blank) from Col A to Col G

Microsoft documentation:

Range.SpecialCells method (Excel)

Range.Find method (Excel)

Sub HighLightBlankRow()
    Dim Sht As Worksheet
    Dim rng As Range, rRow As Range
    Dim lastRow As Long
    For Each Sht In ThisWorkbook.Sheets(Array("Topic 0001", "Topic 0002"))
        Set rng = Nothing
        lastRow = Sht.Cells.Find(What:="*", After:=Sht.Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
        On Error Resume Next
        Set rng = Sht.Range("A2:G" & lastRow).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If Not rng Is Nothing Then
            ' get the rows which have at least a blank cell
            Set rng = Application.Intersect(Sht.Range("A:G"), rng.EntireRow)
            For Each rRow In rng.Rows
                If Application.CountA(rRow) = 0 Then ' all blank cells
                    rRow.Interior.ColorIndex = 15
                End If
            Next
        End If
    Next
End Sub

---

• Highligh blank cells (may not is in completely blank rows) from Col A to Col G.

Sub HighLightBlankCell()
    Dim Sht As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    For Each Sht In ThisWorkbook.Sheets(Array("Topic 0001", "Topic 0002"))
        Set rng = Nothing
        lastRow = Sht.Cells.Find(What:="*", After:=Sht.Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
        On Error Resume Next
        Set rng = Sht.Range("A2:G" & lastRow).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If Not rng Is Nothing Then
            rng.Interior.ColorIndex = 15
        End If
    Next
End Sub