Solved

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

Posted on 2009-04-06
7
613 Views
Last Modified: 2013-11-25
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
Comment
Question by:dhansen_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 24084294
Take a look at the following article.
http://www.cpearson.com/excel/colors.aspx
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24086276
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
 

Author Comment

by:dhansen_
ID: 24097033
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24097108
There is no link as far as I can tell either.
Rory
0
 

Author Comment

by:dhansen_
ID: 24097153
Unable to execute code in break mode and thus cannot answer rorya's q.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 125 total points
ID: 24098910
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question