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]

 

[Lres81715]

Hello lilbuggs,

As you are just wanting the formulas for conditional formatting, here's what I can give you so far

1) =LEN($E1)<>17
This will perform the Conditional Formatting on any Vehicle ID # that isn't 17 characters. Make sure Applies to: =$E:$E or =$E5:$1000 (or whatever row number you want to end on)
2) =SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$A$1:$A$6,$E6)))>0
This will check to see if the Vehicle ID # Has an I, O, Q, U, Z, 0 anywhere in the number. Note: Make a second sheet (Sheet2) and put I, O, Q, etc in A1, A2, A3, etc.
If TRUE, the Conditional Formula will execute it's task.
3)
Let me get back to you shortly. figuring out a better way to do the Matching 10th character in the VIN using =MID($E6,11,1)

 

[salim hasan]

Use this Huge formula in I5 (for the conditional format) drag it down an across to see the Trues and falses
array formula (Crtl+Shift + Enter)

=AND(VLOOKUP(MID(E$5,10,1),{"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"},2,0)*1<>$A5,LEN(TRIM($E5))=17,SUM(IF(ISNUMBER(SEARCH({"i";"o";"q";"u";"z";0},$e5)),1,0))>0)

 

[DRSteele]

I assume you want the vehicle Id# highlighted only when all of those three conditions are specified. I also assume that there are no spaces in the ID#. If that is so, then do the following.

Create this data-reference table somewhere in the workbook. I put mine in O5 through R40 for expediency.

With the first Vehicle id# in cell E5, select all the cells you wish to Format Conditionally and paste this exactly as the rule to invoke the formatting.

=AND(LEN(E5)<>17,SUMPRODUCT(--(ISNUMBER(SEARCH($O$5:$O$10,E5))))<>0,MAX(VLOOKUP(IFERROR(NUMBERVALUE(MID(E5,10,1)),MID(E5,10,1)),$P$11:$R$40,2,0),VLOOKUP(IFERROR(NUMBERVALUE(MID(E5,10,1)),MID(E5,10,1)),$P$11:$R$40,3,0))<>B5)

	O	P	Q	R
I
O
U
K
Z
A
B
C
D
E
F
G
H
J
K
L
M
N
P
R
S
T
V
W
X
Y

<tbody>
[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1980[/TD]
[TD="align: right"]2010[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1981[/TD]
[TD="align: right"]2011[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1982[/TD]
[TD="align: right"]2012[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1983[/TD]
[TD="align: right"]2013[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1984[/TD]
[TD="align: right"]2014[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1985[/TD]
[TD="align: right"]2015[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1986[/TD]
[TD="align: right"]2016[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1987[/TD]
[TD="align: right"]2017[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1988[/TD]
[TD="align: right"]2018[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1989[/TD]
[TD="align: right"]2019[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1990[/TD]
[TD="align: right"]2020[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1991[/TD]
[TD="align: right"]2021[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1992[/TD]
[TD="align: right"]2022[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1993[/TD]
[TD="align: right"]2023[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]2024[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1995[/TD]
[TD="align: right"]2025[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1996[/TD]
[TD="align: right"]2026[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1997[/TD]
[TD="align: right"]2027[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1998[/TD]
[TD="align: right"]2028[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1999[/TD]
[TD="align: right"]2029[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]2030[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2001[/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2002[/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

 

[DRSteele]

My apologies.
I made a mistake in the code; I think it should be:

=AND(LEN(E5)<>17,SUMPRODUCT(--(ISNUMBER(SEARCH($O$5:$O$10,E5))))<>0,NOT(OR(VLOOKUP(IFERROR(NUMBERVALUE(MID(E5,10,1)),MID(E5,10,1)),$P$11:$R$40,2,0)=B5,VLOOKUP(IFERROR(NUMBERVALUE(MID(E5,10,1)),MID(E5,10,1)),$P$11:$R$40,3,0)=B5)))

And if it is the case you want the formatting to trigger when ANY of the three conditions is met, then replace that opening AND with OR.

 

[lilbuggs]

Lres81715,

Are you telling me to just apply these formulas into the conditional formatting portion of Excel or would this be used within the VBA? If within the conditional formatting will this be applied upon opening of the spreadsheet?

 

[lilbuggs]

DRSteele,

Yes I need the conditional formatting to happen when any of the three conditions are met.

 

[DRSteele]

ANY of the conditions? Well I wish you'd have mentioned that in your query.

Anyway, do the following.

Create that data-reference table somewhere in the workbook. I put mine in O5 through R40 for expediency.

With the first Vehicle id# in cell E5, select all the cells you wish to Format Conditionally and paste this exactly as the rule to invoke the formatting.

=OR(LEN(E5)<>17,SUMPRODUCT(--(ISNUMBER(SEARCH($O$5:$O$10,E5))))<>0,NOT(OR(VLOOKUP(IFERROR(NUMBERVALUE(MID(E5,10,1)),MID(E5,10,1)),$P$11:$R$40,2,0)=B5,VLOOKUP(IFERROR(NUMBERVALUE(MID(E5,10,1)),MID(E5,10,1)),$P$11:$R$40,3,0)=B5)))

 

[Lres81715]

You would apply each of those to the conditional formatting portion of Excel. Select the entire column first and then Add New Conditional Format and make a new one for each formatting. VBA is not needed as you said you just wanted Conditional Formatting

*Edit: As I understand it from your most recent post to DRSteele, you want 3 separate formulas or one long one?

 

[lilbuggs]

DRSteele,

I apologize for forgetting to include the word "ANY" in my initial post before I listed out the conditions I needed to meet.