Conditional Format

ChrisMac1

New Member
Joined
Jul 15, 2024
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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
 
Like this?

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


ChrisMac1.xlsm
BC
1Header
2a
3x
4
5c
6w
7f
8s
9a
10a
CompleteAssetFields
Cells with Conditional Formatting
CellConditionCell FormatStop If True
BC2:BC10Expression=MATCH(BC2,Sheet1!$A:$A,0)textNO
 
Upvote 0
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?
 
Upvote 0
1740396889638.png


1740396921555.png
1740396936370.png
 
Upvote 0
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

1740397081366.png


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! :eek:

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)
 
Upvote 0
Solution
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!!
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top