VBA conditional formatting

[Ago]

I have problems with this code.
The code is split in three parts, part one is "constants". And then there is the two ways to do the conditional formatting.
One of them is commented out just to show what is what.


Sub test()
Dim FinalArray(4, 17) As String
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
FinalArray(1, 0) = "A"




' Range(FinalArray(1, 0) & "4:" & FinalArray(1, 0) & LastRow).Select
' Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=( Mid(" & FinalArray(1, 0) & "4; 12; 2)*60*60+Mid(" & FinalArray(1, 0) & "4; 15; 2)*60+Mid(" & FinalArray(1, 0) & "4; 18; 2)+Mid(" & FinalArray(1, 0) & "4; 21; 2)/100)-(Mid(" & FinalArray(1, 0) & "3; 12; 2)*60*60+Mid(" & FinalArray(1, 0) & "3; 15; 2)*60+Mid(" & FinalArray(1, 0) & "3; 18; 2)+Mid(" & FinalArray(1, 0) & "3; 21; 2)/100)" & ">0.201"
' Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
' With Selection.FormatConditions(1).Interior
' .PatternColorIndex = xlAutomatic
' .ThemeColor = xlThemeColorAccent2
' .TintAndShade = 0.399945066682943
' End With
' Selection.FormatConditions(1).StopIfTrue = False
' Range("A1").Select


Range(FinalArray(1, 0) & "4").Select
With Range(FinalArray(1, 0) & "4", Cells(Rows.Count, FinalArray(1, 0)).End(xlUp)).FormatConditions
.Delete
With .Add(Type:=xlExpression, Formula1:="=(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(" & FinalArray(1, 0) & "4;""T"";"" "");""Z"";""""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(" & FinalArray(1, 0) & "3;""T"";"" "");""Z"";"""")))>TIMEVALUE(""0:0:0.201"")")
With .Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
End With
End With
End With
End Sub



The code is just a smal part of a big project.

Now the problem, in 2007 they both work fine.
A friend who has 2010 and 2013 can't get any of them to work.
The upper one is a recorded conditional fromatting in 2010 and changed to suit the project.
I also tried a different way to get the same result by counting what second and fraction of the day it is and compare it.

The conditional format is supposed to detect if "A" column differs more than 0.2 seconds from the previous.

Anyone who can find the problem?

[Bob Phillips]

I just tried it here on Excel 2010, and it worked fine, but I did have to change the formula as I use comma separators, you are using semi-colons. Could it be that your mates are using comma separators like me?

[Ago]

Hmmm... That could be it.

But is that a setting? Can you access that setting as If setting = "," then .... else ....

[snb]

See the attachment

[Ago]

Maybe I'm missing something, but I can't see the difference between your code snb and mine.

[Bob Phillips]

Yes, it is a setting. You can see it by typing


?application.international(xllistseparator)

in the immediate window. You could change all ; in your code to use that value instead.

[snb]

The main point is that I don't use any VBA code; so it will function anywhere.

[Ago]

Yes, it is a setting. You can see it by typing


?application.international(xllistseparator)

in the immediate window. You could change all ; in your code to use that value instead.


Sounds like a good idea.
Will try it out

[Ago]

That seemed to cure the problem!

[Ago]

That seemed to cure the problem!

Thanks