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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Put the folloinw in the Sheet1 module on your spread sheet. Each time a cell is changes in column F, it will check the "Areas" shhet for a match and set the color. If none is found, the interior pattern will be cleared.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AreaSheet As Worksheet
    Dim aRow As Range
    Dim aCell As Range
    Dim Found As Boolean
    ' check if at least one change was in column F
    If (Not Intersect(Columns("F"), Target) Is Nothing) Then
        ' for each cell changed in column F, do the following
        For Each aCell In Target
            If (aCell.Column = 6) Then
 
                ' find the first match with column I of the Areas sheet
                Found = False
                For Each aRow In Worksheets("Areas").Rows
 
                    ' skip row 1
                    If (aRow.Row = 1) Then
 
                    ' stop when no address is in column F
                    ElseIf (aRow.Cells(1, 6).Value = "") Then
                        Exit For
                    Else
 
                        ' a match was found. Make the color of the row the same as the
                        ' interior color of area cell
                        If (aCell.Value = aRow.Cells(1, 9).Value) Then
                            aCell.EntireRow.Interior.Color = aRow.Cells(1, 9).Interior.Color
                            Found = True
                            Exit For
                        End If
                    End If
                Next aRow
 
                ' if no match, then clear the row's color
                If (Not Found) Then
                    aCell.EntireRow.Interior.Pattern = xlNone
                End If
            End If
        Next aCell
    End If
 
 
End Sub
 
Upvote 0
Put the folloinw in the Sheet1 module on your spread sheet. Each time a cell is changes in column F, it will check the "Areas" shhet for a match and set the color. If none is found, the interior pattern will be cleared.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AreaSheet As Worksheet
    Dim aRow As Range
    Dim aCell As Range
    Dim Found As Boolean
    ' check if at least one change was in column F
    If (Not Intersect(Columns("F"), Target) Is Nothing) Then
        ' for each cell changed in column F, do the following
        For Each aCell In Target
            If (aCell.Column = 6) Then
 
                ' find the first match with column I of the Areas sheet
                Found = False
                For Each aRow In Worksheets("Areas").Rows
 
                    ' skip row 1
                    If (aRow.Row = 1) Then
 
                    ' stop when no address is in column F
                    ElseIf (aRow.Cells(1, 6).Value = "") Then
                        Exit For
                    Else
 
                        ' a match was found. Make the color of the row the same as the
                        ' interior color of area cell
                        If (aCell.Value = aRow.Cells(1, 9).Value) Then
                            aCell.EntireRow.Interior.Color = aRow.Cells(1, 9).Interior.Color
                            Found = True
                            Exit For
                        End If
                    End If
                Next aRow
 
                ' if no match, then clear the row's color
                If (Not Found) Then
                    aCell.EntireRow.Interior.Pattern = xlNone
                End If
            End If
        Next aCell
    End If
 
 
End Sub

I was hoping for a Conditional Formatting method. However, I tried your code and it doesn't seem to work. Everything I put in the sheet1 area is always white even though I know it exists in the "areas" sheet. It MIGHT be because the colors on the "Area" sheet come from Conditional formatting rules, and NOT from setting the color manually. I don't know how to get around that though.

Is there a conditional formatting rule that would do what I'm requesting?

Edit: I just tried manually changing the colors on the "areas" sheet and your code did work. However, how do we make it work on cells that are colored by Conditional Formatting rules?
 
Upvote 0
there is one other small issue i run into with that code. If it doesn't match it removes ALL colors from the rows, even the items I want colored. If the code could just skip changing any color if there is no match, that would work.
 
Upvote 0
OK, Which columns do you want to keep the same column and which ones do you want to remove ths color if there is no match. Notice that the line
Code:
aCell.EntireRow.Interior.Pattern = xlNone

is where the entire row has its color removed.
 
Upvote 0
OK, Which columns do you want to keep the same column and which ones do you want to remove ths color if there is no match. Notice that the line
Code:
aCell.EntireRow.Interior.Pattern = xlNone

is where the entire row has its color removed.

Ok, i fixed that line. now, the million dollar question is how do I make this formula work if the colors of the cells on the sheet "Areas", is handled 100% by Conditional Formatting?
 
Upvote 0
After you adjusted the line above, the macro will handle the color of the cells in column F. If you have conditional formatting for other cells it should be working fine. If you have conditional formatting on cells in column F, it will "compete" with this macro and may give unexpected results. I would suggest you remove any conditional foramtting on column F and let this macros do that work for you. If you can attach your latest workbook to a reply and describe what you want happen, we can get make better progress.
 
Upvote 0
After you adjusted the line above, the macro will handle the color of the cells in column F. If you have conditional formatting for other cells it should be working fine. If you have conditional formatting on cells in column F, it will "compete" with this macro and may give unexpected results. I would suggest you remove any conditional foramtting on column F and let this macros do that work for you. If you can attach your latest workbook to a reply and describe what you want happen, we can get make better progress.

I'm no the expert but I've followed your directions to the "T". The result is the same. Can you help just a little more? I've posted a copy of my non-working spreadsheet here. If you can tell me what I'm doing wrong I'd really appreciate it.
 
Upvote 0
OK. Now I see what is happening. Since the interior colors of the cells in "Areas" are determined by conditional formatting, the actual interior color property is not modified, but is the result of the conditional formatting evaluation. Unfortunately, I can see no way to extract the result of the conditional formatting and copy it to another cell. I learned something today.
How about another approach? What if we used a macro on the Areas sheet to replace the conditional formatting and set the actual cell's interior color. That way the result could be copied to Sheet1. This would get a littel more complicated since each time you changed a classification n Areas, you would have to rescan Sheet1 and adjust the colors. This is not impossible, just a bit harder. The logic is something like this:
Code:
On sheet 1 a cell change would execute the macro that I gave you.
 
On the areas sheet:
   If a change is made to any cell in columns A:E or I, then do the following
      if the change is in A:E then perform the cell color changes for the row like the conditional formatting does
      execute the sheet1 macro with the range being the entire column F.
How do you want to proceed?
 
Upvote 0
OK. Now I see what is happening. Since the interior colors of the cells in "Areas" are determined by conditional formatting, the actual interior color property is not modified, but is the result of the conditional formatting evaluation. Unfortunately, I can see no way to extract the result of the conditional formatting and copy it to another cell. I learned something today.
How about another approach? What if we used a macro on the Areas sheet to replace the conditional formatting and set the actual cell's interior color. That way the result could be copied to Sheet1. This would get a littel more complicated since each time you changed a classification n Areas, you would have to rescan Sheet1 and adjust the colors. This is not impossible, just a bit harder. The logic is something like this:
Code:
On sheet 1 a cell change would execute the macro that I gave you.
 
On the areas sheet:
   If a change is made to any cell in columns A:E or I, then do the following
      if the change is in A:E then perform the cell color changes for the row like the conditional formatting does
      execute the sheet1 macro with the range being the entire column F.
How do you want to proceed?

Thanks for looking into this. Let's give your suggestion a try!

Out of curiosity, is CODE slower to execute or is Conditional formatting? these sheets get pretty big (5000-15000 rows) so if the code won't cause a larger slowdown than conditional formatting might, i don't care which method we use to color them.

One other idea, which seems simpler if it's possible is this: Could we use conditional formatting on "Sheet1" based upon a VLOOKUP value in "Areas"? It could check to see where the "X" is in "Areas" Columns A-E, then change the color of the row on "Sheet1" based upon that value.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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