Conditional Formatting duplicating rules

[sam_wright5291]

Hi there, 

I am working with a large data set and wanted to add conditional formatting to help with data validation. 

For example:

I have address that will require different fields to be filled in and I want to highlight the cells that are missing this criteria. 

In the postcode column I would want the cell to highlight in red if a postcode is required and the cell is blank

COUNTRY	POSTCODE
Afghanistan
Argentina	C1053ABR
Argentina

Country	Postal Code
Afghanistan	Optional
Argentina	Required

So I did this formula =IF(VLOOKUP(M7,'Address Requirements'!A:I,9,)="Required",TRUE)*AND(ISBLANK(N7)) 

But each row would have different criteria in terms or whether the postcode was required and if it was blank so I can't work out how to apply conditionally formatting to the column. 

Can anyone help? 

Thanks

Sam

[#2]

Hi Sam

Have attached a spreadsheet that has a solution by adding a code checking column then using conditional formatting to check the result.

Rich