Validate cell value based on index/match or Vlookup using conditional formatting question

Good Morning

Not sure if this is the correct area, but here goes.

I have a data validation drop down list that will populate a range of cells.

The data for the pull down list is located in range AQ1:AQ300

in range AQ1:AR300 I have the Gender of the corresponding name in AQ


Now the pull down list is used in ranges A1:A150 as well as B1:B150

If the Gender of the name dropped into A1:A150 does not equal "M", then fill the cell color RED to show a Female was mistakenly placed in a Male room.

If the Gender of the name dropped into B1:B150 does not equal "F", then fill the cell color RED to show a Male was mistakenly placed in a Female room.


what would the most logical method be to acheive this?

Here's a solution that assumes the mens names go into columnA and the womens into ColumnB.

Select Cells in ColumnA ... starting with cell A1
on Home Tab select "Conditional Formatting"
On Conditional Formatting drop down select "New Rule..."
Select "use formula to determine which cells to format"

copy in this formula =AND(COUNTIFS(AQ:AQ,A1,AR:AR,"M")=0,COUNTIF(AQ:AQ,A1)>0)
click the "format" command button to select format
Click OK


Now do same for ColumnB ... this time use this formula
=AND(COUNTIFS(AQ:AQ,B1,AR:AR,"F")=0,COUNTIF(AQ:AQ,A1)>0)