Ago
02-25-2014, 08:14 AM
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?
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?