roll back excel 2007 macro to work on excel 2003

[richie247]

hi all I have made an excel gantt chart using macros based on conditional formatting, in excel 2007.

what I would like to do is use it on excel 2003 however I am struggling to rewrite the macros to allow approximately 70 conditions per cell with various colours.

this is a sample of what worked in 2007 version.

Range("E12:IK14").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(Inputs!$D$3<=E$1,Inputs!$E$3>=F$1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(Inputs!$g$3<=E$1,Inputs!$h$3>=F$1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
End With

this is repeated many times with additions of a third condition for the formula and a different colour.

i have tried to recreate this in 2003 however I have hit a wall.

i know 2003 and 2007 are old versions and there is not many people still using them but hopefully someone may still be able to help me

 

[Joe4]

Welcome to the Board!

Here is how I usually try to address problems like this.

Turn on the Macro Recorder, and record yourself doing an example of one of these in Excel 2007.
Do the same thing in Excel 2003.

Now, compare the two codes, side-by-side, and look for differences.
Once you find it, update all your existing Excel 2007 code to reflect this. That works most of the time.
Where you sometimes might run into issues is if there are some limits on certain functions in 2003 that didn't exist in 2007 (like if 2003 won't allow 70 conditions).

 

[richie247]

thanks,

I had tried that i could only get it to display only one condition at a time in 2003 is there a way of using multiple and or, or maybe a nested if function. if possible how would it work?

 

[Joe4]

I have never used GANTT charts in Excel, and I don't have Excel 2003 at my disposal, so I am afraid I can't offer much help there.
I was just describing some techniques that I have used over the years when facing the same obstacles of writing VBA to work on different versions of Excel.

 

[richie247]

Ok no problem