Formatting: Formatting based upon cell color on another sheet

[jeffcoleky]

Gurus of the message board, I ask for your free help to save me tremendous time at my job, yet again... I beseech you to solve this problem:

If the value of Column F matches a value on sheet "Areas", color this cell the same color as the one it matches on that sheet.

Here's the workbook data:

Excel 2010
	A	B	C	D	E	F
1	Owner	Parties	Address	City	State	Area
2	Milens Patsy & John	MILENS, PATSY, D; MILENS, PATSY; UNITED STATES OF CALIFORNIA ATTOR, NEY GENERAL; UNITED STATES OF CALIFORNIA US AT, TORNEY CA WESTERN; SMITH FINANCIAL; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF IN, SPECTIONS PERMITS & SANTA CLARA & CARDSTON COUNTY, METROPOLITAN SEWER D, ISTRICT	100 Southern Parkway	Charlotte	NC	103280 / MEADOW CREEK
3	Jordan Smith Sara	DAVIES, JANET, CHARLENE; JORDAN SMITH, JANE, CHARLENE	123 Frankfort	Charlotte	NC	1111173 / CHURCHILL WEST
4	Noman Funding	WEIKELS, JOHN, Jr; WEIKEL, JOHN, J II; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF IN, SPECTIONS; CHASE BANK USA NA; PALUMBO PROPERTIES INC; WASHINGTON MUTUAL BANK FA; T; FREYS, RUTH, T; OM5 OF SANTA CLARA LTD; EDUCATIONAL TRAINING SYSTEMS I, NC; COMPUTER SCHOOL INC	3400 colby	Charlotte	NC	10212377 / NICHOLAS MEADOW
5	HUD	Jones, Melissa, F; Asset Acceptance Llc; National Credit Adjusters Llc; Dh Capital Management Inc	4040 Serene St	Mooresville	NC	1011340 / 4TH & LEE
6	Johnsonton Jacob & Myrtle	JOHNSONTON, CHARLIE; JOHNSONTON, LINDA; COMMONWEALTH OF CALI WORKF, ORCE DEVELOPMENT CAB, INET	123 Smithie	Charlotte	NC	1033182 / PARK STATION
7	Belker Tina and Perez Julio	BELKER, STEVEN; BELKER, TINA	1402 Rammers Rd	Mooresville	NC	30 / COMMERCIAL
8	Smith Lauren & Bill	SMITH, RICHARD, F; SMITH, LAUREN, A; BELGAY AUTO SALES INC; CAPITAL ONE BANK USA NA; CAVALRY SPV I LLC; CONSECO SERVICES LLC; MIDLAND FUNDING LLC; COUNTY OF CARDSTON	100 1st St	Mooresville	NC	1011340 / 4TH & LEE
9	Helenger Family Trust	HELENGER, JAMES, A; HELENGER, DEBORAH, T; MORTGAGE ELECTRONIC REGISTRATI, ON SYSTEMS INC; REPUBLIC BANK & TRUST CO; HELENGER, THOMAS, A; COMMONWEALTH OF CALI DEPAR, TMENT OF REVENUE	100 S Shelby	Charlotte	NC	10442172 / SOUTHERN STATION/SOUTHERN HEIGHTS
10	Werle Jeff & Martha	WERLE, JEFF; WERLE, FEATHER; REPUBLIC BANK & TRUST CO; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF IN, SPECTIONS PERMITS & COMMONWEALTH OF CALI FINAN, CE & ADMINISTRATION, CABINET; CA LIEN HOLDINGS LLC; CITIBANK NA; CITIBANK SOUTH DAKOTA NA	12515 Blackburn	Charlotte	NC	1011340 / 4TH & LEE
11	Smith Bob	Quets, Bob; SMITH Bank Na	1223 Vista Rd	Charlotte	NC	5023331 / SOUTH JTOWN ACREAGE
12	Christian Jessie	JESSIE, CHRISTIAN, M; JESSIE, MELISSA, T	125 W Main	Mooresville	NC	2023142 / BARB/CRITSVILLE/BRADS
13	Ebert Sara	BANK OF CALIFORNIA NA; CA HOME LOANS SERVICING LP; COUNTRYWIDE HOME LOANS SERVICI, NG LP; EBERT, SARA	3000 Wellbrook Place	Charlotte	NC	103280 / MEADOW CREEK
Sheet1

And the next sheet:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Bad	So-So	Boonies	Good	Other	Address Example	City	State	Area	Code	Area Name
2	X					3205 Penhurst Station Rd	Mooresville	NC	103280 / MEADOW CREEK	103280	MEADOW CREEK
3	X					3503 Cotter Ct	Mooresville	NC	1033182 / PARK STATION	1033182	PARK STATION
4		X				2100 Shelbyville Dr	Mooresville	NC	2023142 / BARB/CRITSVILLE/BRADS	2023142	BARB/CRITSVILLE/BRADS
5		X				3816 Southern St	Mooresville	NC	1111173 / CHURCHILL WEST	1111173	CHURCHILL WEST
6		X				2324 Perthe Dr	Mooresville	NC	122280 / SHEPHERDSVILLE	122280	SHEPHERDSVILLE
7			X			13714 S Blake Ln	Mooresville	NC	5023331 / SOUTH JTOWN ACREAGE	5023331	SOUTH JTOWN ACREAGE
8			X			4701 Unsettled Blvd	Mooresville	NC	14A / COMMERCIAL	14A	COMMERCIAL
9				X		2115 Club Vista Parkway	Mooresville	NC	30 / COMMERCIAL	30	COMMERCIAL
10				X		3012 Wellsworthe Rd	Mooresville	NC	10 / COMMERCIAL	10	COMMERCIAL
11				X		4006 Serene Pike	Mooresville	NC	1011340 / 4TH & LEE	1011340	4TH & LEE
12					X	3214 Rolling Creek Ln	Mooresville	NC	10442172 / SOUTHERN STATION/SOUTHERN HEIGHTS	10442172	SOUTHERN STATION/SOUTHERN HEI
13					X	3675 Lentz Park Rd	Mooresville	NC	10223176 / BERRY AVE/HIGHLANDS	10223176	BERRY AVE/HIGHLANDS
14	X					1715 S Samuel Rd.	Mooresville	NC	10212377 / NICHOLAS MEADOW	10212377	NICHOLAS MEADOW
15	X					7413 Hallmark Station Rd	Mooresville	NC	103222281 / MILLER ST/SMITH AVE/STANTON	103222281	MILLER ST/SMITH AVE/STANTON
16	X					1063 Macgyver Ave	Mooresville	NC	2011132228 / SOUTHERN	2011132228	SOUTHERN
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Areas

For example, F2 in my sample data matches I15 on "Areas", and the fill color is Red. So, Row 2 on "Sheet1" should change to Red as well.

The goal would be to be able to add new lines to "Sheet1" and have it automatically change the row color based-upon the Area (Column F) it's in. The final result would look like this.

Excel 2010
	A	B	C	D	E	F
1	Owner	Parties	Address	City	State	Area
2	Milens Patsy & John	MILENS, PATSY, D; MILENS, PATSY; UNITED STATES OF CALIFORNIA ATTOR, NEY GENERAL; UNITED STATES OF CALIFORNIA US AT, TORNEY CA WESTERN; SMITH FINANCIAL; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF IN, SPECTIONS PERMITS & SANTA CLARA & CARDSTON COUNTY, METROPOLITAN SEWER D, ISTRICT	100 Southern Parkway	Charlotte	NC	103280 / MEADOW CREEK
3	Jordan Smith Sara	DAVIES, JANET, CHARLENE; JORDAN SMITH, JANE, CHARLENE	123 Frankfort	Charlotte	NC	1111173 / CHURCHILL WEST
4	Noman Funding	WEIKELS, JOHN, Jr; WEIKEL, JOHN, J II; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF IN, SPECTIONS; CHASE BANK USA NA; PALUMBO PROPERTIES INC; WASHINGTON MUTUAL BANK FA; T; FREYS, RUTH, T; OM5 OF SANTA CLARA LTD; EDUCATIONAL TRAINING SYSTEMS I, NC; COMPUTER SCHOOL INC	3400 colby	Charlotte	NC	10212377 / NICHOLAS MEADOW
5	HUD	Jones, Melissa, F; Asset Acceptance Llc; National Credit Adjusters Llc; Dh Capital Management Inc	4040 Serene St	Mooresville	NC	1011340 / 4TH & LEE
6	Johnsonton Jacob & Myrtle	JOHNSONTON, CHARLIE; JOHNSONTON, LINDA; COMMONWEALTH OF CALI WORKF, ORCE DEVELOPMENT CAB, INET	123 Smithie	Charlotte	NC	1033182 / PARK STATION
7	Belker Tina and Perez Julio	BELKER, STEVEN; BELKER, TINA	1402 Rammers Rd	Mooresville	NC	30 / COMMERCIAL
8	Smith Lauren & Bill	SMITH, RICHARD, F; SMITH, LAUREN, A; BELGAY AUTO SALES INC; CAPITAL ONE BANK USA NA; CAVALRY SPV I LLC; CONSECO SERVICES LLC; MIDLAND FUNDING LLC; COUNTY OF CARDSTON	100 1st St	Mooresville	NC	1011340 / 4TH & LEE
9	Helenger Family Trust	HELENGER, JAMES, A; HELENGER, DEBORAH, T; MORTGAGE ELECTRONIC REGISTRATI, ON SYSTEMS INC; REPUBLIC BANK & TRUST CO; HELENGER, THOMAS, A; COMMONWEALTH OF CALI DEPAR, TMENT OF REVENUE	100 S Shelby	Charlotte	NC	10442172 / SOUTHERN STATION/SOUTHERN HEIGHTS
10	Werle Jeff & Martha	WERLE, JEFF; WERLE, FEATHER; REPUBLIC BANK & TRUST CO; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF IN, SPECTIONS PERMITS & COMMONWEALTH OF CALI FINAN, CE & ADMINISTRATION, CABINET; CA LIEN HOLDINGS LLC; CITIBANK NA; CITIBANK SOUTH DAKOTA NA	12515 Blackburn	Charlotte	NC	1011340 / 4TH & LEE
11	Smith Bob	Quets, Bob; SMITH Bank Na	1223 Vista Rd	Charlotte	NC	5023331 / SOUTH JTOWN ACREAGE
12	Christian Jessie	JESSIE, CHRISTIAN, M; JESSIE, MELISSA, T	125 W Main	Mooresville	NC	2023142 / BARB/CRITSVILLE/BRADS
13	Ebert Sara	BANK OF CALIFORNIA NA; CA HOME LOANS SERVICING LP; COUNTRYWIDE HOME LOANS SERVICI, NG LP; EBERT, SARA	3000 Wellbrook Place	Charlotte	NC	103280 / MEADOW CREEK
Sheet1

 

[blane245]

I used your approach of using conditional formatting completely, but it took a little change to the Areas sheet. I added a column that contained the value (Bad, etc.) of th classification in columns A-E. I then added a row to shhet1 that did a VLookup on the area and copied the classification from the areas sheet. This column was used to do the conditional formatting. See if you like it.

 

[jeffcoleky]

I used your approach of using conditional formatting completely, but it took a little change to the Areas sheet. I added a column that contained the value (Bad, etc.) of th classification in columns A-E. I then added a row to shhet1 that did a VLookup on the area and copied the classification from the areas sheet. This column was used to do the conditional formatting. See if you like it.

I'm OK with the new column on "areas" but i can't afford to add one in "sheet1" unless absolutely necessary. Could we move the vlookup formula into the conditional formatting formula?

Maybe something like: =IF(VLOOKUP($F2,Areas!$I:$L,4,FALSE)="Good","Good","")
Then make a new conditional formatting formula for "Bad" "Other" etc...

 

[blane245]

Yep. If you replace the formulas in the conditional formatting for Sheet1 with something like
=VLOOKUP($F2,Areas!$I:$L,4,false)="Bad"

things will be good. You can eliminate column G in Sheet1.

 

[jeffcoleky]

Yep. If you replace the formulas in the conditional formatting for Sheet1 with something like
=VLOOKUP($F2,Areas!$I:$L,4,false)="Bad"

things will be good. You can eliminate column G in Sheet1.

Yay! it works just the way i need it too. Thanks so much for your time!

 

[blane245]

That is what yu wanted in the first place. At least we got there