Multiple conditions in Excel conditional formatting

excel conditional-statements formula conditional-formatting
Alex Barlow picture Alex Barlow · Aug 13, 2013 · Viewed 82.9k times · Source

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!

Answer

barry houdini picture barry houdini · Aug 13, 2013

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

Related questions

Using OR & AND in COUNTIFS

I would like to include an "AND" condition for one of the conditions I have in my COUNTIFS clause. Something like this: =COUNTIFS(A1:A196;{"Yes"or "NO"};J1:J196;"Agree") So, it should return the number of rows where: (…

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More