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.

[TABLE="width: 632"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Seat
[/TD]
[TD]Year
[/TD]
[TD]Make
[/TD]
[TD]Model
[/TD]
[TD]Vehicle ID #
[/TD]
[TD]Comp
[/TD]
[TD]Coll
[/TD]
[TD]Cost New
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Cap
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]1995
[/TD]
[TD]Chevrolet
[/TD]
[TD]Car
[/TD]
[TD]1G1 JC5243S72185125
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$11,488
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]2000
[/TD]
[TD]Chevrolet
[/TD]
[TD]Car
[/TD]
[TD]1G1JC5247Y7343621
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$12,027
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]2000
[/TD]
[TD]Chevrolet
[/TD]
[TD]Car
[/TD]
[TD]1G1 JC5243Y7346063
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$12,707
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD]1988
[/TD]
[TD]Chevrolet
[/TD]
[TD]Car
[/TD]
[TD]1G1JC5116J7167108
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$5,700
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD]1997
[/TD]
[TD]GMC
[/TD]
[TD]Truck
[/TD]
[TD]1GKDT13W3VK511054
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$14,000
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD]1996
[/TD]
[TD]Dodge
[/TD]
[TD]Truck
[/TD]
[TD]3B7HC13Y2TG144098
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$12,000
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD]1999
[/TD]
[TD]Chevrolet
[/TD]
[TD]Truck
[/TD]
[TD]1GCGK24RXXF079570
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$21,000
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD]1999
[/TD]
[TD]Chevrolet
[/TD]
[TD]Truck
[/TD]
[TD]1GCGK24R5XF080710
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$21,000
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD]2000
[/TD]
[TD]Chevrolet
[/TD]
[TD]Truck
[/TD]
[TD]1GCGK24R6YF500184
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$20,957
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]18
[/TD]
[TD]1999
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBDABK1XH679638
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$49,832
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]64
[/TD]
[TD]1992
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBBNKP3NH395661
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$31,511
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]64
[/TD]
[TD]1992
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBBNKP9NH395664
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$31,511
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]64
[/TD]
[TD]1992
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBBNKP1 NH395660
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$31,511
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]64
[/TD]
[TD]1998
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBBABPXWH623391
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$41,248
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]64
[/TD]
[TD]2001
[/TD]
[TD]Thomas
[/TD]
[TD]Freightliner
[/TD]
[TD]4UZAAXBV91CH56301
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$48,258
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]64
[/TD]
[TD]2000
[/TD]
[TD]Thomas
[/TD]
[TD]Freightliner
[/TD]
[TD]4UZ6CFAA7YCF63566
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$46,638
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]64
[/TD]
[TD]2001
[/TD]
[TD]Thomas
[/TD]
[TD]Freightliner
[/TD]
[TD]4UZAAXBV01CH56302
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$48,258
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]64
[/TD]
[TD]1998
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBBABP7WH623395
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$41,248
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]64
[/TD]
[TD]2000
[/TD]
[TD]Thomas
[/TD]
[TD]Freightliner
[/TD]
[TD]4UZ6CFAA9YCF63567
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$46,638
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]64
[/TD]
[TD]1995
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBBABP9SH616913
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$34,487
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]64
[/TD]
[TD]1998
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBBABPOWH623397
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$41,248
[/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]64
[/TD]
[TD]2000
[/TD]
[TD]Thomas
[/TD]
[TD]Freightliner
[/TD]
[TD]4UZ6CFAAOYCF63568
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$46,638
[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD][/TD]
[TD]1980
[/TD]
[TD]GMC
[/TD]
[TD]Van
[/TD]
[TD]TPT35A3503452
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$9,847
[/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD][/TD]
[TD]1986
[/TD]
[TD]Ford
[/TD]
[TD]Truck
[/TD]
[TD]1FDNF60H5GVA45022
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$7,300
[/TD]
[/TR]
[TR]
[TD]29
[/TD]
[TD]64
[/TD]
[TD]1992
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBBNKP7NH395663
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$31,511
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]64
[/TD]
[TD]1992
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBBPLP7NH452616
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$31,511
[/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD]64
[/TD]
[TD]1992
[/TD]
[TD]Thomas
[/TD]
[TD]International
[/TD]
[TD]1HVBBPLP9NH452620
[/TD]
[TD]$500
[/TD]
[TD]$500
[/TD]
[TD]$31,511
[/TD]
[/TR]
</tbody>[/TABLE]

 

[lilbuggs]

Thank you Lres81717, DRSteele and salim hasan

 

[DRSteele]

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