Multiple conditions in Excel conditional formatting

Question

I'm fairly new to Excel and so not very experienced, but I'm trying to get an entire row to highlight red based on 2 other cells, using conditional formatting. The requirements are for the corresponding M cell in the row to have the value 'N' and for the corresponding E cell to have a past date value '<*NOW'.

I've tried a formula based on an answer for multiple conditions:

=AND(M="(N)";E<*NOW)

A formula for formatting an entire row:

=INDIRECT("m"&ROW())="N",("e"&ROW())E<*NOW

And combining the two:

=AND=INDIRECT("m"&ROW())="N",=INDIRECT("e"&ROW())E<*NOW)

(ignore all the asterisks its the only way i could get the NOW to appear)

None of these are working, please help!

Solution

Assuming your data starts at row 2 then select all rows, e.g. 2 to 100 and then apply the condition for row 2, i.e. use this formula

=AND($M2="N",$E2<TODAY())

That will now apply to the whole range. The $ signs make it format the whole row

Comments

[Alex Barlow]

It's sort of worked. Some of the entries have been filled, yet others haven't, even though they have identical information in columns M and E to some that are.

[Alex Barlow]

I've managed to get it sorted now, thanks, I put in M1 and E1 rather than the cell with the first entry, and that sorted it. Thanks again