Conditional format a cell based on the entry in another cell and also a dropdown...

[charliew]

Hi Guys,

Firstly I'm really sorry if this question has already been asked, i did have a really good rummage and couldn't find anything.

i have a column that I enter a code in (lets say E4), which is the one i want to conditional format.
I want this to be linked to a different cell that COULD have the word "keighley" in it (B1). IF the word "keighley" is in this cell i want this to trigger a list of acceptable codes (on a different sheet) and the the cell I enter the code in (E4) to highlight if not on the list...

Is this even possible?

Could anyone help me please?

Thank you

 

[Toadstool]

If I select E4 then Conditional Formatting with a rule "Use a formula to decide which cells to format" then I use:

=AND(($B$1="keighley"),(ISNA(MATCH($E$4,$G$1:$G$11,0))))

and select the Format required then it will only format E4 if B1="keighley" and the entry in E4 is not in the range of the MATCH.

I put the range to check on the same sheet but it works as well if on a different sheet. I always use an Excel table for a list source so it's dynamically maintained.

[TABLE="class: grid, width: 490"]
<colgroup><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Keyword=
[/TD]
[TD="align: center"]keighley[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Valid Codes
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 1[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Enter Code[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 2[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 3[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 4[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 5[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 6[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 7[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 8[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 9[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code 10[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

For the same layout, here is another CF formula you could use.

Excel Workbook
	A	B	C	D	E	F	G
1	Keyword=	keighley					Valid Codes
2							Code 1
3					Enter Code		Code 2
4					Code 300		Code 3
5							Code 4
6							Code 5
7							Code 6
8							Code 7
9							Code 8
10							Code 9
11							Code 10
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4	1. / Formula is =AND($B$1="keighley",COUNTIF($G$2:$G$11,E4)=0)	Abc

 

[charliew]

This is absolutely perfect! thank you!! Can i add in =if(e4="","",AND(($B$1="keighley"),(ISNA(MATCH($E$4,$G$1:$G$11,0)))) ?????

 

[Peter_SSs]

Does this do what you want?

=AND(E4<>"",$B$1="keighley",COUNTIF($G$2:$G$11,E4)=0)