Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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

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
jgerardy
Asked:
jgerardy
  • 6
  • 5
1 Solution
 
kosmoraiosCommented:
Install Office Service Pack 3. See: http://support.microsoft.com/kb/821292

Hope that helps!
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
jgerardyAuthor Commented:
I am not running XP.
I will try the version sent by Kevin

J
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

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

j
0
 
zorvek (Kevin Jones)ConsultantCommented:
No. But you can specify multiple strings:

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

Kevin
0
 
jgerardyAuthor Commented:
Can I force the formating to the next cell B14?
0
 
zorvek (Kevin Jones)ConsultantCommented:
Yes.

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

Kevin
0
 
jgerardyAuthor Commented:
Sorry Kevin,
Is this a relacement or an addition to the code.  Can you show me where?

j
0
 
zorvek (Kevin Jones)ConsultantCommented:
An addition.

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

Kevin
0
 
jgerardyAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
Sorry about that:

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

Kevin
0
 
jgerardyAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now