Sort and Conditional Format Upon Opening

[lilbuggs]

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 10^th 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>

 

[lilbuggs]

Thank you Lres81717, DRSteele and salim hasan

 

[DRSteele]

You're welcome. [And no apologies necessary! We're all here to help one another.]