Excel VBA Runtime Error 13 - Type Mismatch

[mook25]

Hi all,

I have a VBA script which is designed to conditional format certain cells based on another cells dropdown selection however every time i run it i get the runtime 13 error. Now i know the formula is a big one within this macro however i've been told this is the only way I can get excel to conditionally format using this formula. Can someone help?

I am using excel 2003 if that helps anyone. Thanks

Sub format()
'
' format Macro
' Macro recorded 30/07/2013 by James Pite
'
' Keyboard Shortcut: Ctrl+e
'
    Range("D14:AZ20").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(D$4>=VALUE(LEFT(INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$13,$D$56:$AA$56,0)),FIND(" - ",INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$13,$D$56:$AA$56,0)))-1)),D$4<=VALUE(MID(INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$13,$D$56:$AA$56,0)),FIND(" - ",INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$13,$D$56:$AA$56,0)))+1,255)))"
    With Selection.FormatConditions(1).Font
        .Bold = False
        .Italic = False
        .ColorIndex = 1
    End With
    Selection.FormatConditions(1).Interior.Pattern = xlNone
End Sub

 

[Andrew Poulsom]

I think the Formula1 argument has a maximum length of 255 characters. Yours is 364.

 

[mook25]

I've found out that if I double up the quotes around the FIND argument within the formula I can the get the runtime error to go however it does not xomplete its conditional format, any ideas? thanks

 

[Andrew Poulsom]

If you want to add a conditional formatting formula in VBA you will need to make it less than 256 characters.

 

[Wessie]

I think the Formula1 argument has a maximum length of 255 characters. Yours is 364.

Mook. Andrew gave you the answer here.
Split up that long fomula of yours with "_" and enter the rest into a new line every time.

For example:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(D$4>=VALUE(LEFT(INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$13,$D$56:$AA$56,0)),_
FIND(" - ",INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$13,$D$56:$AA$56,0)))-1)),D$4<=VALUE(MID(INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),_
MATCH($A$13,$D$56:$AA$56,0)),FIND(" - ",INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$13,$D$56:$AA$56,0)))+1,255)))"

Kind regards,
R

 

[Andrew Poulsom]

No that won't work. The formula is still more than 255 characters. I would put the formulas in a spare area on the worksheet and use the results of those cells in the format condition.