PDA

View Full Version : Solved: Conditional formatting, if cell1-cell2>0.2



Ago
03-14-2013, 04:56 AM
I attached a workbook with sample data.
I can't seem to get it correct.

Right now I'm only trying to get it to work, but my final goal is to make the conditional formatting pasted on "A4-A & LastRow".
So if you know how to fix that, I would be very :bow:


Basicly the data is from a GPS.
And I need to color the cell when the data is inaccurate.
Meaning the timedifference is more than 0.2 seconds.

To formula in the conditional formatting now gives the data as MMSS,DD (Minute, Second, Decimal).
I have to use the substitute to get ridd of the ":" between MM and SS.

Now, the problem.
It's correct on A4 and A5, but then it has it's on mind I guess.
And if I select A8 (for example) the formula shows the same as on A4, which it should not.
And if the formula was the same, then shouldn't all rows be red?
I'm very confused about this.

So, what have I done wrong?
And If you could help me, how would I make the conditionall formatting as a VBA code?
From A4 to A & LastRow?

Ago
03-14-2013, 04:57 AM
oops

p45cal
03-14-2013, 08:01 AM
A4 1. / Formula is =(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A4,"T"," "),"Z",""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A3,"T"," "),"Z","")))>TIMEVALUE("0:0:0.201")

p45cal
03-14-2013, 08:22 AM
And If you could help me, how would I make the conditionall formatting as a VBA code?
From A4 to A & LastRow?
Sub blah()
With Range(Range("A4"), Cells(Rows.Count, "A").End(xlUp)).FormatConditions
.Delete
With .Add(Type:=xlExpression, Formula1:="=(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A4,""T"","" ""),""Z"",""""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A3,""T"","" ""),""Z"","""")))>TIMEVALUE(""0:0:0.201"")")
With .Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
End With
End With
End With
End Sub

Ago
03-15-2013, 01:22 AM
Sub blah()
With Range(Range("A4"), Cells(Rows.Count, "A").End(xlUp)).FormatConditions
.Delete
With .Add(Type:=xlExpression, Formula1:="=(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A4,""T"","" ""),""Z"",""""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A3,""T"","" ""),""Z"","""")))>TIMEVALUE(""0:0:0.201"")")
With .Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
End With
End With
End With
End Sub


Thank you very much!
I had to replace the "," to ";" for it to work but thanks!

However there is a problem..
The formatting is not "activated".
If I just run the code, the rule is there but won't trigger.

If I step trough the code, and stop at With .interrior, and open the conditional formatting rule and then close it again, and then run the rest of the code the rule is activated.
And that is odd!

And sometimes it "marks" the wrong rows.
Very odd.

Ago
03-15-2013, 01:28 AM
Here is an example.

I ran the code and this is the result.
Note that some rows has been hidden to make everything fit in the window.

Here A4 should have been red, and A168 should not.



EDIT:
When I paste this formula in a cell:
=(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A168;"T";" ");"Z";""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A167;"T";" ");"Z";"")))>TIMEVALUE("0:0:0.201")
The result is FALSE. Yet the conditional formatting makes it red

p45cal
03-15-2013, 06:51 AM
Unhide all rows before running the macro.
I can't reproduce you non-activation of conditional formatting.
Attached is your file with:
1. All rows unhidden
2. All conditional formatting removed from column A
3. A button (cell A2) which when clicked applies the conditional formatting by running blah.

So see if, when you click the button, all conditional formatting is applied properly or not.

Ago
03-15-2013, 10:39 AM
I will try it when I can :-(
It's weekend tomorrow, and my girlfriend is not very happy with competing with the computer about my attention.

But I did try it with the rows unhidden.
The code is supposed to be used on several GPS datafiles, so this file I attached was just an example.
I found one file, when I changed on row (for example) 10 the value from XX:XX.20 to XX:XX.10 the the conditional formatting changed on row 15.

So it seems to have a mind of it's own.
I will try to get some time to upload the file I found that problem on this weekend.
And try the file you uploaded.

Thank you very much for your help so far!

Ago
03-18-2013, 12:22 AM
Sorry for the long delay in response.

I tried the file you sent me, but it didn't run due to the ","
But when I replaced them to ; it ran but same thing as usual.
the rule is there but nothing is marked, did it again and steped trough the code and opend the CF manage rules in the middle and it worked.


I attached 3 files with GPS data which has the same "issue", and the code I use below.
And the file you uploaded with the code beeing run on my computer.
Try to change one of the timesstamps and you will see that it chages a different row.


Sub test()
With Range(Range("A4"), Cells(Rows.Count, "A").End(xlUp)).FormatConditions
.Delete
With .Add(Type:=xlExpression, Formula1:="=(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A4;""T"";"" "");""Z"";""""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A3;""T"";"" "");""Z"";"""")))>TIMEVALUE(""0:0:0.201"")")
With .Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
End With
End With
End With
End Sub

Ago
03-18-2013, 12:22 AM
file1

Ago
03-18-2013, 12:23 AM
file2

Ago
03-18-2013, 12:24 AM
file3

Ago
03-18-2013, 05:34 AM
OMG!!!

The reason for it not working was a simple Range("A4").Select
The first row had to be selcted for it to work.

Thank you very much for your help p45!