Solved

I receive a Runtime 13 error with conditional formatting code form an expert

Posted on 2008-06-25
12
190 Views
Last Modified: 2011-04-14
I received this code by doing a question search on EE for conditional formatting.
http://search.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21455262.html?sfQueryTermInfo=1+4+condit+format+more+than
It works well in my test workbook, however when I applied it to my working workbook I received the
runtime error 13  Type Mismatch
The debugger stoped at:
Case ""
    Target.Interior.ColorIndex = xlNone
    Target.Font.ColorIndex = xlAutomatic

I am coping information in to my working model and there are no empty fields in my range.
Thanks
j
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, [A15:B800]) Is Nothing Then Exit Sub     'Formatting only applies to cells A15:B800

Select Case Target.Value

Case ""

    Target.Interior.ColorIndex = xlNone

    Target.Font.ColorIndex = xlAutomatic

Case "GP"

     Target.Font.ColorIndex = 5    'Black text

     Target.Font.Bold = True

Case "SPX"

     Target.Font.ColorIndex = 1    'Black text

     Target.Font.Bold = True

Case "HP"

     Target.Font.ColorIndex = 53    'Black text

     Target.Font.Bold = True

Case "SP"

     Target.Font.ColorIndex = 33    'Black text

     Target.Font.Bold = True

Case "AE"

     Target.Font.ColorIndex = 3    'Black text

     Target.Font.Bold = True

Case "WP"

     Target.Font.ColorIndex = 10    'Black text

     Target.Font.Bold = True

Case "EP"

     Target.Font.ColorIndex = 46    'Black text

     Target.Font.Bold = True

Case "MP"

     Target.Font.ColorIndex = 14    'Black text

     Target.Font.Bold = True

Case "RP"

     Target.Font.ColorIndex = 54    'Black text

     Target.Font.Bold = True

Case "JP"

     Target.Font.ColorIndex = 44    'Black text

     Target.Font.Bold = True

Case "JQ"

     Target.Font.ColorIndex = 45    'Black text

     Target.Font.Bold = True
 
 
 

End Select

End Sub

Open in new window

0
Comment
Question by:jgerardy
  • 6
  • 5
12 Comments
 
LVL 6

Expert Comment

by:kosmoraios
ID: 21867824
Install Office Service Pack 3. See: http://support.microsoft.com/kb/821292

Hope that helps!
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21867982
Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Intersect(Target, [A15:B800]) Is Nothing Then Exit Sub     'Formatting only applies to cells A15:B800
For Each Cell In Intersect(Target, [A15:B800])
Select Case Cell.Value
Case ""
    Cell.Interior.ColorIndex = xlNone
    Cell.Font.ColorIndex = xlAutomatic
Case "GP"
     Cell.Font.ColorIndex = 5    'Black text
     Cell.Font.Bold = True
Case "SPX"
     Cell.Font.ColorIndex = 1    'Black text
     Cell.Font.Bold = True
Case "HP"
     Cell.Font.ColorIndex = 53    'Black text
     Cell.Font.Bold = True
Case "SP"
     Cell.Font.ColorIndex = 33    'Black text
     Cell.Font.Bold = True
Case "AE"
     Cell.Font.ColorIndex = 3    'Black text
     Cell.Font.Bold = True
Case "WP"
     Cell.Font.ColorIndex = 10    'Black text
     Cell.Font.Bold = True
Case "EP"
     Cell.Font.ColorIndex = 46    'Black text
     Cell.Font.Bold = True
Case "MP"
     Cell.Font.ColorIndex = 14    'Black text
     Cell.Font.Bold = True
Case "RP"
     Cell.Font.ColorIndex = 54    'Black text
     Cell.Font.Bold = True
Case "JP"
     Cell.Font.ColorIndex = 44    'Black text
     Cell.Font.Bold = True
Case "JQ"
     Cell.Font.ColorIndex = 45    'Black text
     Cell.Font.Bold = True
 
 
 
End Select
Next Cell
End Sub

Kevin
0
 

Author Comment

by:jgerardy
ID: 21868199
I am not running XP.
I will try the version sent by Kevin

J
0
 

Author Comment

by:jgerardy
ID: 21868669
Kevin,
That worked well.  Can I change the Case "GP" to include a wildcard?  I have tried * ? and thy havent worked.

j
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21868758
No. But you can specify multiple strings:

Case "GP", "GX", "GA"

Kevin
0
 

Author Comment

by:jgerardy
ID: 21868779
Can I force the formating to the next cell B14?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21868801
Yes.

   Cell.EntireRow.Columns("B").Interior.ColorIndex =

Kevin
0
 

Author Comment

by:jgerardy
ID: 21868848
Sorry Kevin,
Is this a relacement or an addition to the code.  Can you show me where?

j
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21868879
An addition.

Case "GP"
     Cell.Font.ColorIndex = 5    'Black text
     Cell.EntireRow.Columns("B").ColorIndex = 5
     Cell.Font.Bold = True

Kevin
0
 

Author Comment

by:jgerardy
ID: 21868909
I ran the new code and received a Run Time Error '438'
Object doesn't support the property or method.

at that line

j
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 21868934
Sorry about that:

Case "GP"
     Cell.Font.ColorIndex = 5    'Black text
     Cell.EntireRow.Columns("B").Cells.ColorIndex = 5
     Cell.Font.Bold = True

Kevin
0
 

Author Comment

by:jgerardy
ID: 21868958
You are the expert, but..
It worked when I added .Font after .Cells
Cell.EntireRow.Columns("B").Cells.Font.ColorIndex = 5

Thanks for the help.
j
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now