Link to home
Start Free TrialLog in
Avatar of dhansen_
dhansen_

asked on

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

Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

Take a look at the following article.
http://www.cpearson.com/excel/colors.aspx
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?
Avatar of dhansen_
dhansen_

ASKER

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.
There is no link as far as I can tell either.
Rory
Unable to execute code in break mode and thus cannot answer rorya's q.
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial