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.

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

<colgroup><col><col span="3"><col><col><col></colgroup><tbody>
</tbody>

 

[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)