conditional formatting color changing

[ttowncorp]

I know in conditional formatting your able to change colors in a specific cell. question is how do I have it to change another cell to that color when it changes. for example I have three options in a dropdown list, active, pending and complete. active is red, pending is yellow and complete is green. how do I make one field change color when the statue change if the Items are on the first sheet and the other information is on the second sheet.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD]f
[/TD]
[TD]g
[/TD]
[TD]h
[/TD]
[/TR]
[TR]
[TD]item
[/TD]
[TD]date
[/TD]
[TD]parts
[/TD]
[TD]status
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3002
[/TD]
[TD]11/6/17
[/TD]
[TD]bolt
[/TD]
[TD]active
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6360
[/TD]
[TD]11/2/17
[/TD]
[TD]tire
[/TD]
[TD]pending
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5445
[/TD]
[TD]10/9/17
[/TD]
[TD]cpu
[/TD]
[TD]complete
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7084
[/TD]
[TD]3/15/17
[/TD]
[TD]chair
[/TD]
[TD]complete
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7042
[/TD]
[TD]7/11/17
[/TD]
[TD]bikerack
[/TD]
[TD]active
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7042
[/TD]
[TD]1/1/16
[/TD]
[TD]wires
[/TD]
[TD]pending
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Re: conditional fomratting color changing

Your Conditional Formatting rules can reference other cells using the Formula option.
For example:

=D3="active"

 

[ttowncorp]

Re: conditional fomratting color changing

im sorry but I don't follow. the formula I'm using the code, =IF(VLOOKUP($B2,Sheet2!$D$1:$p$931,13,0)<>"",TRUE). where would I add what you suggested

 

[Joe4]

Re: conditional fomratting color changing

I do not understand your question. I thought you wanted Conditional Formatting to work off of the selection of "Active", "Pending", or "Complete".
So how does your VLOOKUP formula play into that.

I find this wording very vague and confusing:

how do I make one field change color when the statue change if the Items are on the first sheet and the other information is on the second sheet.

How about walking us through an actual example, as it pertains to the image you posted.

 

[ttowncorp]

Re: conditional fomratting color changing

the vlookup is for in my sheet if item "3002" has any info in this case column "C" it will change color. if blank it stays normal. but I want to change that because I'm having conflict when there is two or more of the same item under the same number, it's not changing color, if I have one "3002" that's has nothing in "c" but have another "3002" in a different row and that has info in "c" the vlookup only looks at only one of them which I believe is what it finds first. so I don't want to use that anymore and use it by changing the statue instead. if I change "3002" on sheet2 from active to complete it will highlight that whole row in sheet two that color and on my first sheet that has only the "item numbers change "3002" that color. I'm really not that good in programming when it comes to things like this. i really appreciate your time in helping me figure this out.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]sheet1[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]sheet2[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]items[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]items[/TD]
[TD]date[/TD]
[TD]parts[/TD]
[TD]staus[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3002[/TD]
[TD]11/8/17[/TD]
[TD]bolts[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6360[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6360[/TD]
[TD]11/6/17[/TD]
[TD]tire[/TD]
[TD]pending[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5445[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5445[/TD]
[TD]11/1/17[/TD]
[TD]cpu[/TD]
[TD]complete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7084[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7084[/TD]
[TD]11/2/17[/TD]
[TD]chair[/TD]
[TD]complete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7042[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7042[/TD]
[TD]11/8/17[/TD]
[TD]bikerack[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3002[/TD]
[TD]11/5/17[/TD]
[TD][/TD]
[TD]pending[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Re: conditional fomratting color changing

Sorry, I have been away for a few days.

If you have multiple values that match your lookup value, you are not going to be able to use VLOOKUP. VLOOKUP will only return the first match it finds.
You may have to use something like an INDEX/MATCH function, but those really are not my forte, and with the complexity of your problem, I don't think I can offer much help there.

 

[ttowncorp]

Re: conditional fomratting color changing

thank you