Help with Function in 2003 conversion to 2007

[MachaMacha]

Hi I have a simple function which compares two columns of data and returns the number of differences between the two columns. This worked fine in 2003 but does not work in 2007.

Function CompareArraysM(col1 As Integer, col2 As Integer)
CountSameM = 0
CountDifferentM = 0
For i = 8 To 1514
If Cells(i, col1) = Cells(i, col2) Then
Cells(i, col1).Font.ColorIndex = 0
Cells(i, col2).Font.ColorIndex = 0
CountSameM = CountSameM + 1
Else

Cells(i, col1).Font.ColorIndex = 3
Cells(i, col2).Font.ColorIndex = 3
Cells(i, col1).Interior.ColorIndex = 15
Cells(i, col2).Interior.ColorIndex = 15
CountDifferentM = CountDifferentM + 1
End If
Next i
CompareArraysM = CountDifferentM
End Function

[Bob Phillips]

Looks fine on casual inspection.

What does the 2003 version return, what does the 2007 version not?

[MachaMacha]

2003 returns the number of differences between the two columns, 2007 returns #VALUE

[Bob Phillips]

On a very simple test in 2007 for me, it works fine.

[Dave]

Perhaps you need to specify the sheet? Dave

Sheets("Sheet1").Cells(i, col1).Font.ColorIndex = 0

[Bob Phillips]

Actually, thinking about what Dave says, it would be far beter to have column references than column numbers. Otherwise the function will point at the worng column if another is inserted.

[MachaMacha]

Ok I got it to work this is how I changed the code, although I am confused because it is the same and to me the variable names are just changed but now is working.Function CompareDaysM(col1 As Integer, col2 As Integer)
Dim CountSame As Integer
Dim CountDifferent As Integer
CountSame = 0
CountDifferent = 0
For i = 8 To 2189
If Cells(i, col1) = Cells(i, col2) Then
CountSame = CountSame + 1
Else

CountDifferent = CountDifferent + 1
End If
Next i
CompareDaysM = CountDifferent
End Function

Thanks for your replies!

[mdmackillop]

In my simple test, the code fails on this line, resulting in the error value

Cells(i, col1).Interior.ColorIndex = 15

I guess this has to do with a function not changing other cells, although it does change the font colour in the previous line. As a Sub, this works fine.


Sub test()
CompareArraysM 5, 8
End Sub

Sub CompareArraysM(col1, col2)
CountSameM = 0
CountDifferentM = 0
For i = 8 To 1514
If Cells(i, col1) = Cells(i, col2) Then
Cells(i, col1).Font.ColorIndex = 0
Cells(i, col2).Font.ColorIndex = 0
CountSameM = CountSameM + 1
Else

Cells(i, col1).Font.ColorIndex = 3
Cells(i, col2).Font.ColorIndex = 3
Cells(i, col1).Interior.ColorIndex = 15
Cells(i, col2).Interior.ColorIndex = 15
CountDifferentM = CountDifferentM + 1
End If
Next i
Cells(1, 1) = CountDifferentM
End Sub