Sort and Conditional Format Upon Opening

lilbuggs

New Member
Joined
Aug 25, 2014
Messages
33
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.


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>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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)
 
Upvote 0
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)
Code:
=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)
 
Upvote 0
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.

Code:
=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)

OPQR
5I
6O
7U
8K
9Z
100
11A19802010
12B19812011
13C19822012
14D19832013
15E19842014
16F19852015
17G19862016
18H19872017
19J19882018
20K19892019
21L19902020
22M19912021
23N19922022
24P19932023
25R19942024
26S19952025
27T19962026
28V19972027
29W19982028
30X19992029
31Y20002030
3212001
3322002
3432003
3542004
3652005
3762006
3872007
3982008
4092009

<tbody>
</tbody>
Sheet7
 
Last edited:
Upvote 0
My apologies.
I made a mistake in the code; I think it should be:
Code:
=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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
DRSteele,

Yes I need the conditional formatting to happen when any of the three conditions are met.
 
Upvote 0
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.

Code:
=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)))
 
Last edited:
Upvote 0
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?
 
Last edited:
Upvote 0
DRSteele,

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

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top