Convert macro between Excel versions: from 2010 to 2007 and 2003

[mberon]

Hi all,

I did a macro, it works well in Excel 2010, but in 2007 it triggers "Compile Error (Syntax Error)" on the first IF... THEN, I'd need have the macro working in 2007 and 2003, besides 2010:

Public Sub Color()
Dim DataRange As Range
Dim cell As Range
Set DataRange = Range("A1:ZZ500")
For Each cell In DataRange.Cells
    If (Not IsEmpty(cell)) And (cell.Value = "VERDE") Then
        cell.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
cell.Value = ""
 
    End If
 
    If (Not IsEmpty(cell)) And (cell.Value = "AMARILLO") Then
        cell.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
cell.Value = ""
 
    End If
 
    If (Not IsEmpty(cell)) And (cell.Value = "NARANJA") Then
        cell.Select
   With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
cell.Value = ""
 
    End If
    If (Not IsEmpty(cell)) And (cell.Value = "ROJO") Then
        cell.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
cell.Value = ""
 
    End If
    If (Not IsEmpty(cell)) And (cell.Value = "VERDEAMARILLO") Then
        cell.Select
    With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 135
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .Color = 65535
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .Color = 5296274
        .TintAndShade = 0
    End With
cell.Value = ""
 
    End If
 
    If (Not IsEmpty(cell)) And (cell.Value = "VERDENARANJA") Then
        cell.Select
With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 135
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .Color = 5296274
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .Color = 49407
        .TintAndShade = 0
    End With
cell.Value = ""
 
    End If
 
    If (Not IsEmpty(cell)) And (cell.Value = "NARANJAROJO") Then
        cell.Select
    With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 135
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .Color = 49407
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .Color = 255
        .TintAndShade = 0
    End With
cell.Value = ""
 
    End If
Next
End Sub

_______________________________________________________

Anybody knows how to do this?

Thanks.

 

[RoryA]

You can't do gradient fills in 2003 so that code won't work.

 

[mberon]

OK, but in Excel 2007? I said 2003 to have all options, but I think users will use Excel 2007..

 

[RoryA]

What's the actual error and on which specific line? None of those properties appear to be 2010 specific.

 

[mberon]

It says: Compile Error: Syntax Error, in this line: If (Not IsEmpty(cell)) And (cell.Value = "VERDE") Then . In 2010, this macro works well.

 

[RoryA]

There's nothing about that line that shouldn't work in 2007 (or any other vba-enabled version) but the double test is fairly pointless:

If cell.Value = "VERDE" Then

would achieve the same thing.

 

[stranger3084]

Hi, Just want to confirm if this has been resolved? I'm currently facing the same issues. I created a whole reporting dashboard in Excel 2010 that works with tables, graphs, changing values, etc. Did not realize that everyone else was using Excel 2007. I'm not a beginner but certainly not advanced vba programmer. Re-programming everything will take me ages...

 

[RoryA]

What issue are you facing specifically? There is no guarantee that code written in 2010 will be backward compatible.

 

[jsotola]

try replacing your variable name "cell" with "cel"

cell may be a reserved word


another thing to try

in your code highlight cell.Value right click on it and select "add watch" .... click ok

do same for IsEmpty(cell)

then singlestep through code using F8 and watch the two values in "watch window"

see if the values are as expected isempty(cell) should result in true-false, cell.value should be a valid value (numeric or text)

 

[stranger3084]

What issue are you facing specifically? There is no guarantee that code written in 2010 will be backward compatible.

Can I just create a separate thread? Would not want to create noise in this one... Maybe facing similar issues but for completely different purpose...

Will create new post tomorrow and try to attach my code (quite long though).