Excel - Find Sequential Values with Conditional Formatting

[ommni]

Hello,

I'm try to devise a conditional formatting formula that will highlight all sequential values in a row. Have tried several variations of this formula (which finds dupes) without success:

=COUNTIF(A$7:F$7,A7)>1

Am aware that the solution can be done in VB but writing something from scratch is a bit outside my realm of skill.

Any suggestions or pointers would be very much appreciated.

-Ommni

 

[ommni]

Right on Dom. Formula with the percentage increment works cleanly.

Nice job!

-Ommni

 

[barry houdini]

Barry may come up with something better though

Hey Dom, I tried........

I though there might be something you could do with ABS to shorten the formula but I came up with rounding problems so your suggestion looks pretty good to me.

To avoid highlighting duplicates you could add another condition, i.e.

=AND(ISNUMBER(A7),OR(ISNUMBER(MATCH(A7+0.01,$A7:$F7,0)),ISNUMBER(MATCH(A7-0.01,$A7:$F7,0))),COUNTIF($A7:A7,A7)=1)