VBA - Run-time error 94: Invalid use of Null with Font Color Reference

I have a data set that I am cleaning and one fo the unique features of a row I would like to keep is a specific font color. However, when I refer to Font.ColorIndex in my if statement, I get the error state. Below is my code. Any help would be much appreciated.

Thanks.
Sub Clean_Part1()
Dim i As Integer
Dim key_text As String
Dim fontcolor As Long
 
With Application.ActiveSheet
 
   For i = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row To 1 Step -1
      key_text = Cells(i, 1).Value
      fontcolor = Cells(i, 1).Font.ColorIndex
        If fontcolor <> 10 And InStr(key_text, "Bed") = 0 And InStr(key_text, "Property Type:") = 0 And InStr(key_text, "{") = 0 Then Rows(i).EntireRow.Delete
    Next i
End With
End Sub

Open in new window

dhansen_Asked:
Who is Participating?
 
Rory ArchibaldCommented:
Try running this version - it will tell you the values on error:
 

Sub Clean_Part1()
Dim i As Integer
Dim key_text As String
Dim fontcolor As Long
on error goto err_handle
With Application.ActiveSheet
 
   For i = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row To 1 Step -1
      key_text = Cells(i, 1).Value
      fontcolor = Cells(i, 1).Font.ColorIndex
        If fontcolor <> 10 And InStr(key_text, "Bed") = 0 And InStr(key_text, "Property Type:") = 0 And InStr(key_text, "{") = 0 Then Rows(i).EntireRow.Delete
    Next i
End With
 
exit sub
 
err_handle:
 msgbox "Row " & i & " caused the error"
End Sub

Open in new window

0
 
DhaestCommented:
Take a look at the following article.
http://www.cpearson.com/excel/colors.aspx
0
 
Rory ArchibaldCommented:
I don't think I've ever seen that error before. What is the value of i when it occurs and what is the font colour on that line?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
dhansen_Author Commented:
I am not seeing the connection to the cpearson.com link and my problem. I based my code on that web page to begin with.
0
 
Rory ArchibaldCommented:
There is no link as far as I can tell either.
Rory
0
 
dhansen_Author Commented:
Unable to execute code in break mode and thus cannot answer rorya's q.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.