If (change color) then another cell (change the same color)

April 30, 2013 at 23:18:44
Specs: Windows 7
Hi, I have just basic knowledge of Excel. This is what I want to do: If cell A1 turns yellow, then as a result cell G6 will also turn yellow. Is this possible? What would the formula be? and what are the steps to do it? Can it happen if one cell turns yellow on sheet 2, then as a result have another cell on sheet 1 turns yellow as well?

Now instead of using yellow, I would use the good/bad buttons that turn the cells red or green. Thanks :)


See More: If (change color) then another cell (change the same color)

Report •


#1
May 1, 2013 at 14:45:48
Your looking for Conditional Formatting.

Doing it across sheets, you will need to use a Defined Name.

The general steps for CF are:

Conditional Formatting 2007

1) Select your cell or range of cells, IE A1
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter a formula:

=IF(A1="T",TRUE,FALSE)

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

For CF to display your color the formula MUST return TRUE.

MIKE

http://www.skeptic.com/


Report •

#2
May 1, 2013 at 18:11:15
Ok, I followed your steps. But where do I put the second cell name in the formula to know which other cell to change the same color? And does anyone else know how to make it go across sheets? I see I'm being pointed in the right direction, but I'm still not there yet. Thanks for your help so far :)

Report •

#3
May 1, 2013 at 18:32:43
For the second cell, G6, repeat the same formula as for A1, but Select G6:

Conditional Formatting 2007

1) Select your cell or range of cells, IE >> G6 <<
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter a formula:

=IF(A1="T",TRUE,FALSE)

6) Click on the Format button
7) Select the Fill Tab
8) Select same color as A1
9) Click OK
10) Click OK

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 1, 2013 at 20:40:03
OK, I'm still following your instructions, but nothing is working. I've tried A1 and I've tried substituting the actual cell numbers in the formula and picking the exact same color. But still nothing is happening. =IF(A1="T",TRUE,FALSE) for both cells, and (I'm using different cell examples) =IF(G63="T",TRUE,FALSE) for one cell and =IF(G64="T",TRUE,FALSE) for the other cell. I also am choosing the exact same color. Then when I test it out, I change G63 to the light blue I chose, and G64 doesn't change. :s

Report •

#5
May 2, 2013 at 03:26:51
You should have Two Rules:
One for G63
One for G64

BOTH rules should have the SAME formula:

=IF(G63="T",TRUE,FALSE)

BOTH cells should trigger when you put a letter T in cell G63
do not change the formula for cell G64, it should trigger with
cell G63.

To check, and see what you have:

1) On the ribbon click Conditional Formatting
2) Click on Manage Rules, it’s at the bottom of the dialog box.

At the top of the popup window, in the dropdown box labeled:
Show formatting rules for:
change to show: This Worksheet

Check to make sure BOTH rules are the same as above.

Check in the Applies To box that
the first rule applies to G63
and the second applies to G64

MIKE

http://www.skeptic.com/


Report •

#6
May 2, 2013 at 04:03:20
promoteglobal,

I'm coming in late here, but I have a question.

What Mike is suggesting is a method that will format a cell based on it's contents. Enter a T in the cell and it will be formatted based on what formatting you chose via the Conditional Formatting wizard.

If you are manually setting the fill color and expecting another cell to change color also, then Conditional Formatting will not fit you needs.

I'm just trying to understand your goal. What exactly are you trying to accomplish?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
May 2, 2013 at 11:05:55
Ok, I can't seem to add a screen shot. So I'll make it as detailed as possible. Lets say cell B2 says, "Didn't return students" Then Cell G64 says "Didn't return students" Then I have another two cells B3 and N34 that both say "Communicated with the monitor" I'm hoping to use the Good/Bad buttons at the top of the screen that when I hit them and I have any of the cells highlighted that that highlighted cell will turn green or red depending on what ever I hit. When I say Green or Red, I'm talking about fill in color, not the actual color of the text. What I hope to do is add a formula to B2 and/or G64, so that if I make B2 becomes green or red, that it will also make G64 green or red. Then the second pair I hope for the same thing. If I make B3 green or red, the N34 will also become green or red depending on what I pick. I just want the color to match. The wordings, the formulas, and the matching cells, once all set up, will always remain the same. I will how ever change all the color back to "no fill" and do it all over again, and again, and again. So only the color will change.

Report •

#8
May 2, 2013 at 11:51:14
There is no in-cell formula, that, by itself, can change the fill color of a cell. In-cell formulas can only return a value in the cell in which it resides.

What Mike has been suggesting is Conditional Formatting, where a TRUE/FALSE formula (a "Rule") is used to test the value in a cell and format the cell with a chosen fill color when the rule is TRUE.

This may still work for you, but it might be a bit more complicated.

In your first post, and in your latest post, you mention "Good/Bad buttons" and that you want these to trigger some occurrence. What are these "Good/Bad buttons" that you speak of?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
May 2, 2013 at 12:19:43
In Excel 2010, if you open up to the home tab and full screen. Look to the right by styles, in the same section as conditional formating and format as table, you will see 6 buttons. They are Normal, Bad, Good, Neutral, Calculation, and Check Cell. If you click on Normal and you have a cell highlighted it will turn white. If you click Bad, the cell will turn red. If Neutral=Tan, If Calculation=light Gray, If Check cell= a dark gray.

Report •

#10
May 2, 2013 at 13:04:32
Those are Style Buttons, and are used to apply several formats in one step, and to make sure that cells have consistent formatting.

See here:

http://office.microsoft.com/en-us/e...

MIKE

http://www.skeptic.com/


Report •

#11
May 2, 2013 at 13:34:48
I guess you could use this macro.

After you click the "Good/Bad button" on a given cell, leave that cell as active and run the macro. Any cell that contains the same value as the active cell will be set to the same style.

Again, there is no formula that you can "add" to cell to make it change colors or match another cell's color.

Sub CopyStyle()
   For Each cell In ActiveSheet.UsedRange
    If cell.Value = ActiveCell.Value Then
     cell.Style = ActiveCell.Style
    End If
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
May 2, 2013 at 21:14:38
But does this change everything at once/match everything at once?..do I copy and paste this code like this?

Right click Sheet 1, Hit view code, copy/paste code..then go back to spreadsheet? Nothing happened doing it that way.


Report •

#13
May 3, 2013 at 04:00:49
I'm not sure what you mean by "change everything at once".

Let's say you select a cell that contains "Didn't return students". You then click the "Bad button" to turn it red. You then run the macro with the cell still selected and all cells on that sheet that contain "Didn't return students" will turn red. The code simply checks each cell on the sheet and if it finds a cell that contains the same value as the selected cell, it sets the style to be the same as the original cell.

If you want it run across all sheets, we'll need to add a couple of lines of code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
May 3, 2013 at 12:40:58
Ok, So since I wanted to get started on my work in making things easier for me, I've created a system that will work. But I still would like to know how to do this task. I'm going to use it to create a data base, which would require knowing how to do this over sheets, and I would like to try and just hit a button and it would match up everything at once. To define all at once, to answer your question...I select Didn't return students" cell click bad or good. the cell changes style. Then I click "greeted students well" with good button. I finish the sheet with goods and bads..then I run the macro and it matches everything up. Or when I run the macro, the cell has to be selected in order for it to work? How are is it to run the macro? Do I click 1 button? or do I have to copy and paste the code every time?

Report •

#15
May 4, 2013 at 04:25:10
It looks like I am going to have to put together a detailed explanation of how the macro needs to be set up and run. Unfortunately I am travelling this weekend and probably won't have much time to write them up, test them, etc.

I'll see what I can do but it might be a couple of days before I get to it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question