Change sheet2 column A red if the text appears in sheet1 column A

[bino1121]

looking for a formula or conditional formatting so that if the text building1 shows up on sheet1 then the value on sheet2 that is building1 changes red

conversely, if the text on sheet1 does not show up in the column A on sheet2 (building1 is not on sheet1 but is on sheet2) then the color changes to green.

Example 1A&B

Example 2A&B

 

[DanteAmor]

Hello @bino1121 , thanks for posting on MrExcel Board.

The conditional format goes on the sheets "EX1B" and "EX2B"

Example 1:

Dante Amor
	A
1	building1
2	building3
3	building5
EX1A

Dante Amor
	A
1	building1
2	building2
3	building3
4	building4
5	building5
EX1B

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A5	Expression	=COUNTIF(EX1A!$A:$A,$A1)	text	NO

-----
Example 2:

Dante Amor
	A
1	building1
2	building2
3	building3
EX2A

Dante Amor
	A
1	building3
2	building4
3	building5
EX2B

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A3	Expression	=COUNTIF(EX2A!$A:$A,$A1)=0	text	NO

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[bino1121]

Hello @bino1121 , thanks for posting on MrExcel Board.

The conditional format goes on the sheets "EX1B" and "EX2B"

Example 1:

Dante Amor
	A
1	building1
2	building3
3	building5
EX1A

Dante Amor
	A
1	building1
2	building2
3	building3
4	building4
5	building5
EX1B

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A5	Expression	=COUNTIF(EX1A!$A:$A,$A1)	text	NO

-----
Example 2:

Dante Amor
	A
1	building1
2	building2
3	building3
EX2A

Dante Amor
	A
1	building3
2	building4
3	building5
EX2B

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A3	Expression	=COUNTIF(EX2A!$A:$A,$A1)=0	text	NO

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

I'm confused on how you set this up? I tried it in my conditional formatting and it didn't work?

 

[DanteAmor]

You just have to copy the examples on your sheets.

Or download this file with my example, there you can see both formats.

Loading Google Sheets