roll back excel 2007 macro to work on excel 2003

richie247

New Member
Joined
Feb 3, 2017
Messages
14
Office Version
365, 2013
Platform
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,416
Office Version
365
Platform
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
14
Office Version
365, 2013
Platform
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
51,416
Office Version
365
Platform
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.
 

Forum statistics

Threads
1,081,536
Messages
5,359,372
Members
400,525
Latest member
swwber

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top