If cell D3 equals X and P3 doesnt equal 'z' highlight it

Hi,

Doing some rule based checking, on Excel.
In column D i have types of tenures ie social, leashold, affordable & in column P i have types of tenancies ie Assure, Fixed, Starter, Secure.
Only some of the tenure types match the tenancy types.

So i need a formula that says 'IF D3 = social, then P3 must equal Assure OR Secure, If it is not equal highlight BOTH cells in red.'

Please see attached file for an example of the layout. Excel Forum example.xlsx

Is this possible?

Thanks,

Originally Posted by Kaper

use conditional formatting with rule based on formula for both cells.
and the formatting formula is:

Formula:

Please Login or Register  to view this content.

well, I probably misunderstood when is this THEN.

now it is highlighted for D3 = social, then P3 must equal Assure OR Secure
if other way round so for instance red when D3 = social, and P3 different from Assure OR Secure change the conditional formatting formula to (both cells)

Formula:

Please Login or Register  to view this content.

Thanks for your response,

Im new to formulas and conditional formatting so not entirely sure what i am meant to do next.

Do i create a rule under conditional formatting using the above formula?

Thank-you for your time.

Originally Posted by Suffolkhousing

Thanks for your response,

Im new to formulas and conditional formatting so not entirely sure what i am meant to do next.

Do i create a rule under conditional formatting using the above formula?

Thank-you for your time.

Bump......

Originally Posted by Kaper

use conditional formatting with rule based on formula for both cells.
and the formatting formula is:

Formula:

Please Login or Register  to view this content.

well, I probably misunderstood when is this THEN.

now it is highlighted for D3 = social, then P3 must equal Assure OR Secure
if other way round so for instance red when D3 = social, and P3 different from Assure OR Secure change the conditional formatting formula to (both cells)

Formula:

Please Login or Register  to view this content.

Just tried to use the forumla and it works to an extent, however if the cell DOESNT have social in it, it will remove the information from the cell and replace it with social. It doesnt highlight it, So i know what ones have changed, but with 3000 properties i need to find a way that doesnt change the data.

Thank-you.

Thanks for your help. All sorted now.