Conditional or VBA help!

[Anniema]

Per the image, I am trying to have numbers colour code themselves when entered into specific cells (in this instance in Columns G & O). What colour is dependent on a "Bin" number, bins are in columns A & B, therefore when a bin number is added to the table e.g. 203 in the In table (but could equally be the Out table) it would turn yellow, and eg 302 in the out table bin column auto turns blue. I have looked at various conditional format options to no avail as I do not want to highlight duplicates or lesser/greater than etc options so am struggling to see how to achieve the required results.

 

[StephenCrump]

Welcome to the Forum!

You can use a formula in conditional formatting - it's the last option in the dropdown.

So you could do something like this:

	A	B	C	D	E	F	G
1	Bin 1	Bin 2
2	203	201
3	205	202
4	207	204
5	209	206
6	etc	etc
7							Bin
8							203
9							201
10							999
11							209
12
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G8:G11	Expression	=COUNTIF($B$2:$B$6,G8)	text	NO
G8:G11	Expression	=COUNTIF($A$2:$A$6,G8)	text	NO

 

[Anniema]

Thank you so much for your prompt response. I shall away and try it now.

 

[kevin9999]

Not sure what you meant by this:

I do not want to highlight duplicates

but if you mean that you only want to highlight the found values once, then you could use this variation on @StephenCrump 's solution:

Book1
	A	B	C	D	E	F	G
1	Bin 1	Bin 2
2	203	201
3	205	202
4	207	204
5	209	206
6	etc	etc
7							Bin
8							203
9							201
10							999
11							209
12							201
13							206
14							206
15
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G8:G15	Expression	=AND(COUNTIF($A$2:$A$6,G8),COUNTIF($G$8:$G8,G8)<=1)	text	NO
G8:G15	Expression	=AND(COUNTIF($B$2:$B$6,G8),COUNTIF($G$8:$G8,G8)<=1)	text	NO

 

[StephenCrump]

Not sure what you meant by this ...

I had the same thought initially, but I think it means not being able to use the built-in conditional formatting rules:

 

[kevin9999]

You're probably right Stephen

 

[Anniema]

Welcome to the Forum!

You can use a formula in conditional formatting - it's the last option in the dropdown.

So you could do something like this:

	A	B	C	D	E	F	G
1	Bin 1	Bin 2
2	203	201
3	205	202
4	207	204
5	209	206
6	etc	etc
7							Bin
8							203
9							201
10							999
11							209
12
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G8:G11	Expression	=COUNTIF($B$2:$B$6,G8)	text	NO
G8:G11	Expression	=COUNTIF($A$2:$A$6,G8)	text	NO

Worked perfectly, thank you.