asked on

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/questions/21455262/Automatic-formatting-based-on-more-than-4-conditions.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

kosmoraios

Install Office Service Pack 3. See: http://support.microsoft.com/kb/821292

Hope that helps!

zorvek (Kevin Jones)

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

jgerardy

ASKER

I am not running XP.
I will try the version sent by Kevin

J

jgerardy

ASKER

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

j

zorvek (Kevin Jones)

No. But you can specify multiple strings:

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

Kevin

jgerardy

ASKER

Can I force the formating to the next cell B14?

zorvek (Kevin Jones)

Yes.

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

Kevin

jgerardy

ASKER

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

j

zorvek (Kevin Jones)

An addition.

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

Kevin

jgerardy

ASKER

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

at that line

j

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jgerardy

ASKER

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