Simple Conditional Formats using VLOOKUP

[BrooksTech]

I can do complex spreadsheets but never seem to get the syntax right on conditional formats, especially when it includes a vlookup:

Skipping straight to Manage Conditional Formats "IF" statement:

1. I need to say IF the value in A2 = (any value found in Array in Sheet2 column B) then format RED, else leave alone.

2. I need to do a separate one where its THREE options, IF $B2>=0 but $B2<=5 then format ORANGE, if $B2<0 format RED, otherwise leave alone.

Im familiar with how to construct the logic, but formatting likes the syntax a little differently and thats what Im not getting.

 

[Yongle]

To colour the values in SheetName 2 if they match value in A2 in SheetName 1

Select whole of column B in Sheet2 \ click Home Tab \ Condtional formatting \ New Rule \ Use formula... \ FORMULA: =B1='SheetName 1'!$A$2


Excel 2016 (Windows) 32 bit

	A​	B​
1​
2​	Title2
3​

Sheet: SheetName 1

Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​	Book	Chapter Name
2​	Book 001	Title1
3​	Book 002	Title2
4​	Book 003	Title2
5​	Book 004	Title1
6​	Book 005	Title2
7​	Book 006	Title1
8​	Book 007	Title1
9​	Book 008	Title2
10​	Book 009	Title2
11​	Book 010	Title3
12​	Book 011	Title3
13​	Book 012	Title3
14​

Sheet: SheetName 2

 

[Yongle]

To colour A2 in SheetName 1 if value found anywhere in column B in SheetName 2

Select A2 in Sheet1 \ click Home Tab \ Condtional formatting \ New Rule \ Use formula... \ FORMULA: =COUNTIF('SheetName 2'!$B:$B,$A$2)>1


Note
If A2 is in the same sheet as column B then remove reference to sheet in both posts #2 and #3