# Formatting: Formatting based upon cell color on another sheet

#### jeffcoleky

##### Active Member
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.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### blane245

##### New Member
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
aCell.EntireRow.Interior.Pattern = xlNone
End If
End If
Next aCell
End If

End Sub``````

#### jeffcoleky

##### Active Member
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
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?

#### jeffcoleky

##### Active Member
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.

#### blane245

##### New Member

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.

#### jeffcoleky

##### Active Member
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?

#### blane245

##### New Member

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.

#### jeffcoleky

##### Active Member
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.

#### blane245

##### New Member
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?

#### jeffcoleky

##### Active Member
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.

Replies
0
Views
130
Replies
12
Views
426
Replies
5
Views
970
Replies
13
Views
1K
Replies
3
Views
336

1,130,210
Messages
5,640,865
Members
417,174
Latest member
diegomuser

### 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.

### Which adblocker are you using?

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

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