Apr 18 2019 08:17 PM
Hi everybody. Hopefully someone will provide the answer:
Cells in column A, starting at A1, have the following as an example:
A 01
A 02
A 03
A 04
A 06
A 07
A 15
A 16
A 17
A 18
A 19
A 19
A 20
A 21
A 29
A 30
A 31
A 33
A 34
The number of rows will vary. The column will always be sorted ascending. I want to be able to check all the cells for sequence continuity. Whenever a break in that continuity is found then the last cell before the break in sequence will be highlighted light red as a fill colour. There might also be an occasional duplicate value. When duplicates are encountered the second occurrence should be ignored for sequence continuity checking but its cell highlighted yellow.
Thanks for any assistance
Apr 18 2019 09:33 PM
Hi@Chalky1955,
This is can be done using some sort of formulas in Conditional Formatting rules.
Rule 1 (duplicate value):
=COUNTIF($A$1:A1,A1)>1
Rule 2 (sequence continuity checking):
=AND(ABS(A2-A1)>1,ABS(A2-A1)<>A1)
Hope that helps