Conditional Format

[ChrisMac1]

Hi there,

I have a workbook called Complete_Asset_Fields.xlsx which is made up of two sheets, CompleteAssetFields and Sheet1.

I need to highlight all the values in column BC on the sheet CompleteAssetFields, that also appear in column A on Sheet1.

Can you help me with the formula to use in Conditional formatting please?

Many thanks,
Chris

 

[Peter_SSs]

Like this?

ChrisMac1.xlsm
	A
1	Data
2	a
3	b
4	c
5	d
6	e
7	f
Sheet1

ChrisMac1.xlsm
	BC
1	Header
2	a
3	x
4
5	c
6	w
7	f
8	s
9	a
10	a
CompleteAssetFields

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BC2:BC10	Expression	=MATCH(BC2,Sheet1!$A:$A,0)	text	NO

 

[ChrisMac1]

Hi Peter, thanks for the speedy response.

So I highlighted column BC on sheet CompleteAssetFields, then chose Conditional Formatting and created a new rule by formula. I inserted =MATCH(BC2,Sheet1!$A:$A,0) and set it to fill a green colour.

The result I got was that it highlighted the values on the sheet CompleteAssetFields that are not on Sheet1, but I need it to highlight those values that are on Sheet1 please. Did I do something wrong?

 

[ChrisMac1]

 

[Peter_SSs]

Did I do something wrong?

Yes, you did.
You will see in my mini sheet that my CF is applied to col BC, starting at row 2

By selecting the whole column you are starting at row 1, but still using the formula for row 2. The result is that all your matches will be one row off.

I would recommend that you do not use a whole column for your conditional formatting. CF is volatile meaning that any time anything in your worksheet causes a recalculation, Excel will need to check the CF in all 1,048,576 cells in the column when I'm sure that you will have nothing like that amount of data!

I would just apply it to the required area, or if that might change, to something that will easily cover any amount of data you will likely get. So if you have a few hundred rows at most I would apply it down to, say, row 1,000

Whatever range you do choose to apply it to, once you have selected that range, the formula that you enter into the CF dialog should be the formula for the "active cell". So if you did select the whole column, BC1 would be the active cell so the formula should have been
=MATCH(BC1,Sheet1!$A:$A,0)

 

[ChrisMac1]

Ahhhh yes, makes sense now, thanks for the explanation... I went with just updating the formula =MATCH(BC1,Sheet1!$A:$A,0).

Thanks so much as always Peter!!

 

[Peter_SSs]

Cheers. You're welcome.