Formatting: Formatting based upon cell color on another sheet

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:


Excel 2010
ABCDEF
1OwnerPartiesAddressCityStateArea
2Milens Patsy & JohnMILENS, 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, ISTRICT100 Southern ParkwayCharlotteNC103280 / MEADOW CREEK
3Jordan Smith SaraDAVIES, JANET, CHARLENE; JORDAN SMITH, JANE, CHARLENE123 FrankfortCharlotteNC1111173 / CHURCHILL WEST
4Noman FundingWEIKELS, 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 INC3400 colbyCharlotteNC10212377 / NICHOLAS MEADOW
5HUDJones, Melissa, F; Asset Acceptance Llc; National Credit Adjusters Llc; Dh Capital Management Inc4040 Serene StMooresvilleNC1011340 / 4TH & LEE
6Johnsonton Jacob & MyrtleJOHNSONTON, CHARLIE; JOHNSONTON, LINDA; COMMONWEALTH OF CALI WORKF, ORCE DEVELOPMENT CAB, INET123 SmithieCharlotteNC1033182 / PARK STATION
7Belker Tina and Perez JulioBELKER, STEVEN; BELKER, TINA1402 Rammers RdMooresvilleNC30 / COMMERCIAL
8Smith Lauren & BillSMITH, 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 CARDSTON100 1st StMooresvilleNC1011340 / 4TH & LEE
9Helenger Family TrustHELENGER, JAMES, A; HELENGER, DEBORAH, T; MORTGAGE ELECTRONIC REGISTRATI, ON SYSTEMS INC; REPUBLIC BANK & TRUST CO; HELENGER, THOMAS, A; COMMONWEALTH OF CALI DEPAR, TMENT OF REVENUE100 S ShelbyCharlotteNC10442172 / SOUTHERN STATION/SOUTHERN HEIGHTS
10Werle Jeff & MarthaWERLE, 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 NA12515 BlackburnCharlotteNC1011340 / 4TH & LEE
11Smith BobQuets, Bob; SMITH Bank Na1223 Vista RdCharlotteNC5023331 / SOUTH JTOWN ACREAGE
12Christian JessieJESSIE, CHRISTIAN, M; JESSIE, MELISSA, T125 W MainMooresvilleNC2023142 / BARB/CRITSVILLE/BRADS
13Ebert SaraBANK OF CALIFORNIA NA; CA HOME LOANS SERVICING LP; COUNTRYWIDE HOME LOANS SERVICI, NG LP; EBERT, SARA3000 Wellbrook PlaceCharlotteNC103280 / MEADOW CREEK
Sheet1


And the next sheet:


Excel 2010
ABCDEFGHIJK
1BadSo-SoBooniesGoodOtherAddress ExampleCityStateAreaCodeArea Name
2X3205 Penhurst Station RdMooresvilleNC103280 / MEADOW CREEK103280MEADOW CREEK
3X3503 Cotter CtMooresvilleNC1033182 / PARK STATION1033182PARK STATION
4X2100 Shelbyville DrMooresvilleNC2023142 / BARB/CRITSVILLE/BRADS2023142BARB/CRITSVILLE/BRADS
5X3816 Southern StMooresvilleNC1111173 / CHURCHILL WEST1111173CHURCHILL WEST
6X2324 Perthe DrMooresvilleNC122280 / SHEPHERDSVILLE122280SHEPHERDSVILLE
7X13714 S Blake LnMooresvilleNC5023331 / SOUTH JTOWN ACREAGE5023331SOUTH JTOWN ACREAGE
8X4701 Unsettled BlvdMooresvilleNC14A / COMMERCIAL14ACOMMERCIAL
9X2115 Club Vista ParkwayMooresvilleNC30 / COMMERCIAL30COMMERCIAL
10X3012 Wellsworthe RdMooresvilleNC10 / COMMERCIAL10COMMERCIAL
11X4006 Serene PikeMooresvilleNC1011340 / 4TH & LEE10113404TH & LEE
12X3214 Rolling Creek LnMooresvilleNC10442172 / SOUTHERN STATION/SOUTHERN HEIGHTS10442172SOUTHERN STATION/SOUTHERN HEI
13X3675 Lentz Park RdMooresvilleNC10223176 / BERRY AVE/HIGHLANDS10223176BERRY AVE/HIGHLANDS
14X1715 S Samuel Rd.MooresvilleNC10212377 / NICHOLAS MEADOW10212377NICHOLAS MEADOW
15X7413 Hallmark Station RdMooresvilleNC103222281 / MILLER ST/SMITH AVE/STANTON103222281MILLER ST/SMITH AVE/STANTON
16X1063 Macgyver AveMooresvilleNC2011132228 / SOUTHERN2011132228SOUTHERN
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
ABCDEF
1OwnerPartiesAddressCityStateArea
2Milens Patsy & JohnMILENS, 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, ISTRICT100 Southern ParkwayCharlotteNC103280 / MEADOW CREEK
3Jordan Smith SaraDAVIES, JANET, CHARLENE; JORDAN SMITH, JANE, CHARLENE123 FrankfortCharlotteNC1111173 / CHURCHILL WEST
4Noman FundingWEIKELS, 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 INC3400 colbyCharlotteNC10212377 / NICHOLAS MEADOW
5HUDJones, Melissa, F; Asset Acceptance Llc; National Credit Adjusters Llc; Dh Capital Management Inc4040 Serene StMooresvilleNC1011340 / 4TH & LEE
6Johnsonton Jacob & MyrtleJOHNSONTON, CHARLIE; JOHNSONTON, LINDA; COMMONWEALTH OF CALI WORKF, ORCE DEVELOPMENT CAB, INET123 SmithieCharlotteNC1033182 / PARK STATION
7Belker Tina and Perez JulioBELKER, STEVEN; BELKER, TINA1402 Rammers RdMooresvilleNC30 / COMMERCIAL
8Smith Lauren & BillSMITH, 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 CARDSTON100 1st StMooresvilleNC1011340 / 4TH & LEE
9Helenger Family TrustHELENGER, JAMES, A; HELENGER, DEBORAH, T; MORTGAGE ELECTRONIC REGISTRATI, ON SYSTEMS INC; REPUBLIC BANK & TRUST CO; HELENGER, THOMAS, A; COMMONWEALTH OF CALI DEPAR, TMENT OF REVENUE100 S ShelbyCharlotteNC10442172 / SOUTHERN STATION/SOUTHERN HEIGHTS
10Werle Jeff & MarthaWERLE, 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 NA12515 BlackburnCharlotteNC1011340 / 4TH & LEE
11Smith BobQuets, Bob; SMITH Bank Na1223 Vista RdCharlotteNC5023331 / SOUTH JTOWN ACREAGE
12Christian JessieJESSIE, CHRISTIAN, M; JESSIE, MELISSA, T125 W MainMooresvilleNC2023142 / BARB/CRITSVILLE/BRADS
13Ebert SaraBANK OF CALIFORNIA NA; CA HOME LOANS SERVICING LP; COUNTRYWIDE HOME LOANS SERVICI, NG LP; EBERT, SARA3000 Wellbrook PlaceCharlotteNC103280 / MEADOW CREEK
Sheet1
 
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.
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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...
 
Last edited:
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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