jeffcoleky
Active Member
- Joined
- May 24, 2011
- Messages
- 274
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:
And the next sheet:
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.
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 |