Conditional Formatting from a set of data

[ClarkRD6]

Hi There,
I'm trying to figure out how to use conditional formatting if a number is true in a range of data.

Below are examples of the data. On the first tab 'Pipeline' is there a way to highlight the data in Column B if it equals one of the values in a range of data such as Column E on the Targeted Census Tracts Tab?
Pipeline Tab:

Targeted Census Tract Tab:

 

[kevin9999]

Welcome to Mr Excel! The short answer is yes, but could you please post your sheets using the XL2BB add in, or alternatively share your workbook via Google Drive, Dropbox or similar file sharing platform. If you share, remember to make the file available to anyone with the link.

 

[Joe4]

Welcome to the Board!

Select range B2 to last cell in column B with data on your "Pipeline" sheet, go to Conditional Formatting, select "New Rule", and chose the "Use a formula to determine which cells to format" and enter this formula:

=COUNTIF('Targeted Census Tracts'!B:B,$B2)>0

Then select your desired formatting option/color and click "OK".

That should do what you want.

 

[kevin9999]

Similar approach to Joe (slightly different formula) would look like this in practice:

Book1
	A	B	C	D	E
1	Year	MSA	state	county	tract
2					1015.00
3					1018.00
4					1052.00
5					1015.00
6					1033.50
7					1036.90
8					1040.30
9					1043.70
10					1047.10
11					1050.50
12					1053.90
13					1057.30
14					1060.70
15					1064.10
16					1067.50
17					1070.90
18					1074.30
19					1077.70
20					1073.00
Targeted Census Tracts

Book1
	A	B
1	address	tract
2		1000.00
3		1000.00
4		1000.00
5		1000.00
6		1000.00
7		1043.70
8		1047.10
9		1050.50
10		1053.90
11		1057.30
12		1011.00
13		1011.00
14		1011.00
15		1011.00
16		1011.00
17		1011.00
18		1011.00
19		1011.00
20		1011.00
Pipeline

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B20	Expression	=MATCH($B2,'Targeted Census Tracts'!$E:$E,0)	text	NO

 

[ClarkRD6]

Similar approach to Joe (slightly different formula) would look like this in practice:

Book1
	A	B	C	D	E
1	Year	MSA	state	county	tract
2					1015.00
3					1018.00
4					1052.00
5					1015.00
6					1033.50
7					1036.90
8					1040.30
9					1043.70
10					1047.10
11					1050.50
12					1053.90
13					1057.30
14					1060.70
15					1064.10
16					1067.50
17					1070.90
18					1074.30
19					1077.70
20					1073.00
Targeted Census Tracts

Book1
	A	B
1	address	tract
2		1000.00
3		1000.00
4		1000.00
5		1000.00
6		1000.00
7		1043.70
8		1047.10
9		1050.50
10		1053.90
11		1057.30
12		1011.00
13		1011.00
14		1011.00
15		1011.00
16		1011.00
17		1011.00
18		1011.00
19		1011.00
20		1011.00
Pipeline

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B20	Expression	=MATCH($B2,'Targeted Census Tracts'!$E:$E,0)	text	NO

This worked!!! Thank you so much, i've been researching this for months and got closed but could never figure it out. I wasn't able to get Joe's formula to work, although I was not receiving an error, so it was picking something up but it wouldn't pull through the way this formula did. I cannot thank you enough!

 

[Joe4]

I wasn't able to get Joe's formula to work, although I was not receiving an error, so it was picking something up but it wouldn't pull through the way this formula did. I cannot thank you enough!

The most common issue people have is they do not align their formula with the range they selected.

For example, instead of selecting from cell B2 to the last row with data in column B, they will select the ENTIRE column B.
However, since the formula I gave starts with looking at B2, the formula and range will be out of alignment (i.e. cell B1 looking at B2, cell B2 looking at B3, etc).
The formula needs to be written to look at the very first cell in the range you selected, and it will adjust itself for all other cells.

 

[kevin9999]

This worked!!! Thank you so much, i've been researching this for months and got closed but could never figure it out. I wasn't able to get Joe's formula to work, although I was not receiving an error, so it was picking something up but it wouldn't pull through the way this formula did. I cannot thank you enough!

Glad we were able to help and thanks for the feedback Also, please note @Joe4's advice regarding the common issues with CF.