2007 excel to 2003 compatibility issue - runtime 935 error

[mook25]

Hi, I have a VBA script which is to run a formula from another sheets cell so as to conditionally format cells on another sheet however the script was written in excel 2007 and i am restricted to excel 2003.

Because of this i'm getting the runtime error 935 which i'm told is down to the compatability issue beteween the 2 versions of excel. I know there are work arounds but i am not a VBA expert. Can anyone help? Thanks

Sub SetConditional()</SPAN>
   
    With Selection</SPAN>
      .FormatConditions.Add Type:=xlExpression, Formula1:=ActiveWorkbook.Sheets("Sheet1").Range("A1").Formula</SPAN>
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority</SPAN>
      With .FormatConditions(1).Interior</SPAN>
        .Pattern = xlSolid</SPAN>
        .PatternColorIndex = xlAutomatic</SPAN>
        .ColorIndex = xlAutomatic</SPAN>
      End With</SPAN>
      .FormatConditions(1).StopIfTrue = False</SPAN>
    End With</SPAN>
   
End Sub</SPAN>

 

[RoryA]

Try removing these two lines:

.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

and

      .FormatConditions(1).StopIfTrue = False

 

[mook25]

Hi, By doing that I now have the error 'Runtime error 9 - Subscript out of range'?

 

[RoryA]

On which line? It works for me in 2003 (assuming you have a Sheet1)

 

[mook25]

Ok i did mess up a bit there and was changing the range thinking it was the sheet name.....it's been a long day. lol

I have corrected and now get the runtime error 1004 - Application defined or object defined error' when accessing debug it highlights the line below.

.FormatConditions.Add Type:=xlExpression, Formula1:=ActiveWorkbook.Sheets("Sheet1").Range("A1").Formula

could you paste your version after edits here, if it works for you, just in case i'm missing something?

Thanks

 

[RoryA]

My version is the same. You would get an error if you already had three formats in place in 2003, or if the formula in A1 wasn't valid.

 

[mook25]

There is defo no other formats in place and as far as i am aware the formula is correct. I think i'll try to get an updated version of excel at home and forward on from work to home and try again there to see if i can correct the problem. Thanks for your help though.