I have a spreadsheet based on vehicles that needs to be sorted upon opening by year, make, model, and VIN.
After the sort is completed, I need to have conditional formatting performed on the VIN number. I need the VIN number to be highlighted if it meets the following conditions:
1. Is greater than or less than 17 characters
2. Has an I, O, Q, U, Z, 0 anywhere in the number
3. If the 10th character does not correlate with the year of the vehicle based on the following list
A = 1980
B = 1981
C = 1982
D = 1983
E = 1984
F = 1985
G = 1986
H = 1987
J = 1988
K = 1989
L = 1990
M = 1991
N = 1992
P = 1993
R = 1994
S = 1995
T = 1996
V = 1997
W = 1998
X = 1999
Y = 2000
1 = 2001
2 = 2002
3 = 2003
4 = 2004
5 = 2005
6 = 2006
7 = 2007
8 = 2008
9 = 2009
A = 2010
B = 2011
C = 2012
D = 2013
E = 2014
F = 2015
G = 2016
H = 2017
J = 2018
K = 2019
L = 2020
M = 2021
N = 2022
P = 2023
R = 2024
S = 2025
T = 2026
V = 2027
W = 2028
X = 2029
Y = 2030
Below is a copy of what the spreadsheet looks like with column and row numbers. Any help possible on this would be great.
<tbody>
</tbody>
After the sort is completed, I need to have conditional formatting performed on the VIN number. I need the VIN number to be highlighted if it meets the following conditions:
1. Is greater than or less than 17 characters
2. Has an I, O, Q, U, Z, 0 anywhere in the number
3. If the 10th character does not correlate with the year of the vehicle based on the following list
A = 1980
B = 1981
C = 1982
D = 1983
E = 1984
F = 1985
G = 1986
H = 1987
J = 1988
K = 1989
L = 1990
M = 1991
N = 1992
P = 1993
R = 1994
S = 1995
T = 1996
V = 1997
W = 1998
X = 1999
Y = 2000
1 = 2001
2 = 2002
3 = 2003
4 = 2004
5 = 2005
6 = 2006
7 = 2007
8 = 2008
9 = 2009
A = 2010
B = 2011
C = 2012
D = 2013
E = 2014
F = 2015
G = 2016
H = 2017
J = 2018
K = 2019
L = 2020
M = 2021
N = 2022
P = 2023
R = 2024
S = 2025
T = 2026
V = 2027
W = 2028
X = 2029
Y = 2030
Below is a copy of what the spreadsheet looks like with column and row numbers. Any help possible on this would be great.
| A | B | C | D | E | F | G | H |
| Seat | Year | Make | Model | Vehicle ID # | Comp | Coll | Cost New |
| Cap | |||||||
5 | 1995 | Chevrolet | Car | 1G1 JC5243S72185125 | $500 | $500 | $11,488 | |
6 | 2000 | Chevrolet | Car | 1G1JC5247Y7343621 | $500 | $500 | $12,027 | |
7 | 2000 | Chevrolet | Car | 1G1 JC5243Y7346063 | $500 | $500 | $12,707 | |
8 | 1988 | Chevrolet | Car | 1G1JC5116J7167108 | $500 | $500 | $5,700 | |
9 | 1997 | GMC | Truck | 1GKDT13W3VK511054 | $500 | $500 | $14,000 | |
10 | 1996 | Dodge | Truck | 3B7HC13Y2TG144098 | $500 | $500 | $12,000 | |
11 | 1999 | Chevrolet | Truck | 1GCGK24RXXF079570 | $500 | $500 | $21,000 | |
12 | 1999 | Chevrolet | Truck | 1GCGK24R5XF080710 | $500 | $500 | $21,000 | |
13 | 2000 | Chevrolet | Truck | 1GCGK24R6YF500184 | $500 | $500 | $20,957 | |
14 | 18 | 1999 | Thomas | International | 1HVBDABK1XH679638 | $500 | $500 | $49,832 |
15 | 64 | 1992 | Thomas | International | 1HVBBNKP3NH395661 | $500 | $500 | $31,511 |
16 | 64 | 1992 | Thomas | International | 1HVBBNKP9NH395664 | $500 | $500 | $31,511 |
17 | 64 | 1992 | Thomas | International | 1HVBBNKP1 NH395660 | $500 | $500 | $31,511 |
18 | 64 | 1998 | Thomas | International | 1HVBBABPXWH623391 | $500 | $500 | $41,248 |
19 | 64 | 2001 | Thomas | Freightliner | 4UZAAXBV91CH56301 | $500 | $500 | $48,258 |
20 | 64 | 2000 | Thomas | Freightliner | 4UZ6CFAA7YCF63566 | $500 | $500 | $46,638 |
21 | 64 | 2001 | Thomas | Freightliner | 4UZAAXBV01CH56302 | $500 | $500 | $48,258 |
22 | 64 | 1998 | Thomas | International | 1HVBBABP7WH623395 | $500 | $500 | $41,248 |
23 | 64 | 2000 | Thomas | Freightliner | 4UZ6CFAA9YCF63567 | $500 | $500 | $46,638 |
24 | 64 | 1995 | Thomas | International | 1HVBBABP9SH616913 | $500 | $500 | $34,487 |
25 | 64 | 1998 | Thomas | International | 1HVBBABPOWH623397 | $500 | $500 | $41,248 |
26 | 64 | 2000 | Thomas | Freightliner | 4UZ6CFAAOYCF63568 | $500 | $500 | $46,638 |
27 | 1980 | GMC | Van | TPT35A3503452 | $500 | $500 | $9,847 | |
28 | 1986 | Ford | Truck | 1FDNF60H5GVA45022 | $500 | $500 | $7,300 | |
29 | 64 | 1992 | Thomas | International | 1HVBBNKP7NH395663 | $500 | $500 | $31,511 |
30 | 64 | 1992 | Thomas | International | 1HVBBPLP7NH452616 | $500 | $500 | $31,511 |
31 | 64 | 1992 | Thomas | International | 1HVBBPLP9NH452620 | $500 | $500 | $31,511 |
<tbody>
</tbody>