Conditional Formatting - Apply format if to both cells if one cell meets condition

[GAJITCS]

As per the title, I have two cells, B2:C2 that I need to apply Conditional Formatting to.

Where B2=A2, both B2 and C2 need to have the same formatting applied. Cells are on different worksheets as below.

Worksheet 1
A2=Apples

Worksheet 2
B2=Apples
C2=0.5

Many thanks.

 

[steve the fish]

Try this formula:

=AND($B2<>"",$B2=Sheet1!$A2)

applied to B2:C2

 

[Marcílio_Lobão]

GAJITCS, Good morning.

steve he fish, Greetings from Brazil.

GAJITCS, I believe it is not possible to directly reference other tabs in conditional formatting formulas.

But you can fool Excel in this case.

Create a Named Range with cell A2 of Sheet1.

Example: Sheet1!$A$2 ---> ORIGIN

Apply in the formula suggested above:

= AND($B$2<>"", $B$2= ORIGIN)

Please, tell us if it worked as you want.

I hope it helps.

 

[steve the fish]

What i suggested works as intended Marcilio.

 

[GAJITCS]

Try this formula:

=AND($B2<>"",$B2=Sheet1!$A2)

applied to B2:C2

Can't get that one to work.

 

[steve the fish]

Ok explain the steps you took as that isnt much to go on.

 

[GAJITCS]

My bad... Was basing on of Cell Value, not formula.

Now working as you originally posted. Many thanks.

 

[GAJITCS]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Bananas[/TD]
[TD].6[/TD]
[TD]Apples[/TD]
[TD].2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Carrots[/TD]
[TD].4[/TD]
[TD]Bananas[/TD]
[TD].35[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD].49[/TD]
[TD]Apples[/TD]
[TD].4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Carrots[/TD]
[TD].28[/TD]
[TD]Bananas[/TD]
[TD].25[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Bananas[/TD]
[TD].17[/TD]
[TD]Carrots[/TD]
[TD].23[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples[/TD]
[TD]Bananas[/TD]
[TD]Carrots[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 1

Cells with Apples are to be filled Green, Bananas, Yellow and Carrots are Orange

Steve The Fish's solution worked when I was dealing with just columns B and C on sheet 2. I want to carry the same formatting over to Columns D and E.

When I use Format Painter, I get exactly the same formatting as in columns B and C, line for line though the content of cells in D has changed.

Is this down to Absolute referencing? If I take out the $, I lose the formatting in cells C and E.

Any ideas?|

Many Thanks.

 

[Marcílio_Lobão]

What i suggested works as intended Marcilio.

Steve the fish, Good afternoon.

My observation is due to the fact that it was mentioned that two different tabs will be used in conditional formatting.

"Cells are on different worksheets as below."

Worksheet 1
A2=Apples

Worksheet 2
B2=Apples
C2=0.5

I still believe that it is not possible to use conditional formatting while using references from more than one tab.

Please let me know if I'm really correct or not.
Learning more is always my goal.

Have a nice day!