Conditional Formatting not working as it should - logic wrong?

[saraapple]

I am having a nightmare with what should be a straight forward task.
I have created two conditional formatting rules - which work (they change colour) but it makes no sense as to why?

These are the formulas:

Delivery Schedule TEMPLATE UPDATED 210221.xlsm
	K
2	KIRKCALDY
Delivery Schedule Stoke

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:V101	Expression	=COUNTIF(OUTPUT!K:V,Suppliers)>0	text	NO
K2:V101	Expression	=AND(K2<>"",MATCH($K2,SALVAGE,0))	text	NO

I want them to look at two lists I have created "Salvage" and "Suppliers" then flag them in the colours.
Excel accepts the formulas but all the cells end up formatted wrong, so Kirkcaldy is not on the suppliers list so should not be in red type.

Am I missing something obvious as I have been working on this for hours and getting nowhere!

Thank you for taking the time to look
Sara

 

[RicoS]

Why have you chosen two different formulas to express similar things? Your countif formula looks to be the wrong way round, you'd want to look for K2 in Suppliers I think? You could use that same formula in the Salvage conditional format

=COUNTIF(Suppliers,K2)>0

=COUNTIF(Salvage,K2)>0

The other thing is to make sure that you are in the top cell of your list (K2) when you type in the formula, or it tends to go wrong.

They'll be dancing in the streets of Raith if you manage to get it to work (for any residents of Kirkcaldy looking in).

 

[Fluff]

How about

=COUNTIF(Suppliers,$K2)>0

 

[saraapple]

Getting there....
I used the formulas you both suggested with a little change to ignore the blank cells as follows:

Delivery Schedule TEMPLATE UPDATED 210221 (Autosaved).xlsm
	K
2	KIRKCALDY
Delivery Schedule Stoke

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:V101	Expression	=COUNTIF(Suppliers,$K2)>0	text	NO
K2:V101	Expression	=AND(K2<>"",(COUNTIF(SALVAGE,$K2)>0))	text	NO

Suppliers has failed to turn any cells to red type.
Salvage has filled in the stores green on the salvage list correctly with the exception of two cells which turn green despite them not being on the list?!?!

Any ideas?

 

[Fluff]

I hadn't noticed that the CF was looking at a range of columns so you need to remove the $ sign

 

[RicoS]

If you try typing in the formula into the next available column in the same row as your data and see if it works correctly. If so, copy that formula into the conditional formatting, making sure you select the full range (K2:V101), but still have K2 as the "current" (white) cell.
Also, you shouldn't need the and condition in the second expression. Unless you have a Salvage list that has a bunch of blanks in it?

 

[saraapple]

Perfect!
Thank you both for your assistance, and thank you [U]RicoS[/U] for the tip on checking the formula as the data rather than going straight for conditional formatting

 

[Fluff]

Glad we could help & thanks for the feedback.