roll back excel 2007 macro to work on excel 2003

richie247

New Member
Joined
Feb 3, 2017
Messages
16
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2017
Messages
16
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,877
Messages
5,574,771
Members
412,617
Latest member
mlharris
Top