excel cell color based on second cell color

Microsoft Excel 2007 home and student
January 31, 2010 at 21:55:22
Specs: Windows 7, AMDII2.20GHz/6GB
Hi! I would like to change cell A1 color if any one cell between B1-Z1 changes color. If one of those cells turns red, A1 should turn red. Same if they turn green or yellow. Thanks!

See More: excel cell color based on second cell color

Report •

#1
February 1, 2010 at 04:58:13
Hi,

What color do you expect cell A1 to be if for example B1 is red, C1 is yellow and D1 is green,
or
do all the cells B1 to Z1 always have the same color
or
do you want cell A1 to respond to the most recent change in color in any cell in the range B1 to Z1

Regards


Report •

#2
February 1, 2010 at 07:11:42
Only one cell between B1 and Z1 will have a color. A1 should reflect the same color.

Report •

#3
February 1, 2010 at 08:58:44
How was the color of that single cell created?

If it was "filled" via the fill color drop down or a macro, then there will be a different solution than if the color was changed by Conditional Formatting.

My assumption is that you are going to need a macro do to do this and VBA does not look at Conditionally Formatted cells in the same way it looks at Filled cells.


Report •

Related Solutions

#4
February 1, 2010 at 10:26:42
The cell is filled by conditional formatting.

Report •

#5
February 1, 2010 at 10:58:29
It might help if you provided just a few more details about your spreadsheet and how it works, but I'll toss this out as a possible solution.

You could loop through the range until you find the cell that meets one if the conditions you used in your CF.

For example, let's say your CF conditions are:

If cell = 1, CF = Red
If cell = 2, CF = Yellow
If cell = 3, CF = Green

Since you said that only one cell will be colored at any given time, then only one cell could contain 1, 2 or 3 at any given time.

This code will check each cell in A1:Z1 and as soon as it finds a 1, 2 or 3 it will fill A1 accordingly.

Note: This code is not concerned with your conditional formatting. It is simply looking for the same values that caused your Conditional Formats to be applied. Even if you removed or changed all of your CF conditions, this code would still fill A1 based on it finding a 1, 2 or 3.

If you need to determine which CF condition is applied to a given cell, things get much more complicated.

To use the code offered below,

- Right click the sheet tab for the sheet you are working with
- Choose view code and paste this code in the pane that opens.
- Modify the Case statements to match your actual conditions, or tell us what your exact conditions are, and we'll suggest the changes required.

The code will fire whenever you make a change to the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
'Loop through range B1:Z1
   For myCol = 2 To 26
'If a 1, 2 or 3 is found, set A1 Fill Color
    Select Case Cells(1, myCol)
'1 = Red
     Case 1
      Range("A1").Interior.ColorIndex = 3
      Exit Sub
'2 = Yellow
     Case 2
      Range("A1").Interior.ColorIndex = 6
      Exit Sub
'3 = Green
     Case 3
      Range("A1").Interior.ColorIndex = 4
      Exit Sub
'1, 2 or 3 not found? Clear A1
     Case Else
      Range("A1").Interior.ColorIndex = -4142
    End Select
   Next
End Sub


Report •

#6
February 1, 2010 at 12:50:47
Hey thanks for that info. The only problem is that it only turned cells red, not yellow or green. I will give you some more particulars and see if that will help. The column I am trying to make change color is B numbers 4-35. They will change based on one cell in C-AK having a 1, 2, or 3.Row 4 would be based on C4-AK4, row 35 would be based on C35-AK35...get the idea? Also there are some hiddent columns within the C-AK that I would like to be not included in the formula. If that is too hard, I can put them some where else. The hidden columns are D,F,H,J,L.N,P,R,T,V,X,Z,AB,AD,AF,AH,and AJ. Thanks lots!

Report •

#7
February 1, 2010 at 13:03:08
In your OP you said:

"I would like to change cell A1 color if any one cell between B1-Z1 changes color."

In response 6 you said:

"The column I am trying to make change color is B numbers 4-35."

Color me confused.


Report •

#8
February 1, 2010 at 19:31:48
LOL Sorry! When I first posted the sheet was in the works and I wanted an idea on how to make the cells change color. I didn't have any idea it was so complicated. Response 6 is the final deal. Please don't see red. ;)

Report •

#9
February 2, 2010 at 05:10:21
re: Response 6 is the final deal

But what is the final deal?

As an example, are you asking that:

B4 should be Red if any cell in C4:AK4, ignoring the hidden columns that you listed, is equal to 1.

B4 should be Yellow if any cell in C4:AK4, ignoring the hidden columns that you listed, is equal to 2.

B4 should be Green if any cell in C4:AK4, ignoring the hidden columns that you listed, is equal to 3.

In addition, can we assume that if any of the cells being checked in C4:AK4 is equal to 1, 2 or 3, then no other cell in that row will be equal to either of the other 2 numbers. In other words, if there is a 1 in H4 then there will not be a 2 or 3 in any of the other cells in C4:AK4, ignoring the hidden columns?

Are you using Conditional Formatting to set colors in C4:AK4 or are you asking for Conditional Formatting to be used for Column B only.

Finally, are you trying to do this on a Row by Row basis, such that B4:B35 could be a mixture of Red, Yellow and Green based on what is found in each respective Row?


Report •

#10
February 2, 2010 at 15:41:32
If I understood the last post correctly, you were right on. Ignoring the hidden columns, there will be only one number in one paticular row. I am not using conditional formating. I tried to use it to change B4:B35 but couldn't figure it out. B4:B35 can be a mixture of different colors based on what is found in each respective row. If it would make it easier to understand, create a temporary email address and I could email the sheet to you.

Report •

#11
February 2, 2010 at 16:57:29
If I understand your layout correctly, this code should handle the task.

If not, let me know.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRow, nxtCol
'Turn off ScreenUpdating to prevent flashing
 Application.ScreenUpdating = False
'Reset B4:B35 to no Fill
  Range("B4:B35").Interior.ColorIndex = -4142
'Loop through range C4:AK35 Skipping hidden rows
   For nxtRow = 4 To 35
    For nxtCol = 3 To 37 Step 2
'If Column B in this row is already filled, stop checking this row
     If Range("B" & nxtRow).Interior.ColorIndex <> -4142 Then Exit For
'If a 1, 2 or 3 is found, set Column B Fill Color
    Select Case Cells(nxtRow, nxtCol)
'1 = Red
     Case 1
      Range("B" & nxtRow).Interior.ColorIndex = 3
      Exit For
'2 = Yellow
     Case 2
      Range("B" & nxtRow).Interior.ColorIndex = 6
      Exit For
'3 = Green
     Case 3
      Range("B" & nxtRow).Interior.ColorIndex = 4
      Exit For
    End Select
   Next
  Next
'Turn ScreenUpdating back on
 Application.ScreenUpdating = True
End Sub


Report •

#12
February 3, 2010 at 05:37:12
Thank you! Thank you! Thank you! It works! Thanks for all your hard work and for putting up with my inacurate descriptions of what I needed! God bless!

Report •

#13
February 3, 2010 at 05:44:59
I'm glad it worked out for you.

You should study the code and try to understand how it works in case you need to make changes to your spreadsheet.

Since the code is very specific to certain ranges, values and colors, any changes you make to the spreadsheet layout could seriously impact the results of the macro.

Come on back if you have any questions.


Report •

Ask Question