Home

Conditional Formatting duplicating rules

sam_wright5291
Occasional Visitor

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

 

 

COUNTRYPOSTCODE
Afghanistan 
ArgentinaC1053ABR
Argentina 

 

CountryPostal Code
AfghanistanOptional
ArgentinaRequired

 

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

 

1 Reply

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

Related Conversations
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
conditional formatting with formula
kdwork in Excel on
3 Replies
Mail from internal user moved to junk and can not move back
whyou in Exchange on
3 Replies
Rule to save attachment
Tim Hunter in Outlook on
1 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies