How to change the color of one cell based on next cell

March 28, 2018 at 01:14:12
Specs: Windows 7
Hi
I have a column with conditional formatting which changes it color if that cell contains a letter then it turns to blue and if the cell contains "-" then it turns to green. now I want to change the color of cell j based on k color is k1 is green j1 should be green. if k1 is blue then j1 should be blue.

See More: How to change the color of one cell based on next cell

Reply ↓  Report •

#1
March 28, 2018 at 04:06:49
Use the same rules that you set for the Column K cells in Column J. Select the Column K cells and set the rule to refer to the cells in Column J.

In fact, you should be able to select both the Column J and K cells at the same and when you write the rule, use $J which should lock to the rule to the Column J cells but also CF the K cells based on Column J.

(I don't have access to Excel right now, so I can't post an example.)

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

message edited by DerbyDad03


Reply ↓  Report •

#2
March 28, 2018 at 18:51:43
Hello bro
I have used conditional formating for the cell which contains method rather than using a formula. So can you help me telling the formula. Thanks for the reply

Reply ↓  Report •

#3
March 28, 2018 at 19:20:57
Please explain what you mean by "if that cell contains a letter".

Do you mean any letter or a specific letter or certain specific letters?

Once I understand exactly what you are trying to do, it will be easier for me to offer a solution.

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


Reply ↓  Report •

Related Solutions

#4
April 18, 2018 at 00:10:42
Why would you want to change the color of cell j1 based on the color of cell k1?
I think what DerbyDad03 is suggesting is that you set conditional formatting to J column for the same conditions that you used for column K.

Better yet, extend the area on which you apply the condition for column K also to J. You just have to go to Conditional formatting - Manage rules and in the "Applies to" field, replace

=$K$1:$K$1000
with
=$J$1:$K$1000
so that the same conditions applying for K will apply for J.

message edited by Mrrrr


Reply ↓  Report •

#5
April 18, 2018 at 05:50:39
Mrrr,

Even though I don't think the OP is coming back, based on his response to my suggestion I think I understand why "set conditional formatting to J column for the same conditions that you used for column K" didn't sit well with him.

He said: "I have used conditional formating for the cell which contains method"

I believe that he is currently using one of the canned rules, specifically "Format only cells that contain".

Using that "method", as he calls it, you can't format one cell based on the contents of another since you can't reference the other cell. He needs to move to a formula based CF, which I'm sure either you or I could help him with. However, since he hasn't come back and told us what his exact requirements are, it's kind of hard for us to help him.

His loss. ;-)

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


Reply ↓  Report •

#6
April 18, 2018 at 22:25:59
Oh, I didn't check the dates of this post and its answers (3 weeks ago). My bad.

Yes, it's his loss. :)


Reply ↓  Report •

Ask Question