• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:

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

0
dhansen_
Asked:
dhansen_
  • 3
  • 2
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now