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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Title2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: SheetName 1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Book[/td][td]Chapter Name[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Book 001[/td][td]Title1[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Book 002[/td][td=bgcolor:#9BC2E6]Title2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Book 003[/td][td=bgcolor:#9BC2E6]Title2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Book 004[/td][td]Title1[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Book 005[/td][td=bgcolor:#9BC2E6]Title2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Book 006[/td][td]Title1[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Book 007[/td][td]Title1[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Book 008[/td][td=bgcolor:#9BC2E6]Title2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]Book 009[/td][td=bgcolor:#9BC2E6]Title2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Book 010[/td][td]Title3[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]Book 011[/td][td]Title3[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]Book 012[/td][td]Title3[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: SheetName 2[/td][/tr][/table]

 

[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