Conditional formatting: highlighting rows based on condition

Question

Suppose I have the following table:

Date       Value
5/27/2017  5
6/10/2017  7
6/24/2017  8
7/8/2017   5
7/22/2017  10

I want to highlight the most recent row, meaning the row with the date closest to today. I know I need to use Excel's conditional formatting capability, so I created the indicator column _inRange, which derives from this function:

=IF(AND(A2 <= TODAY()), TODAY() < A3), 1, 0)

The new table:

Date Value _inRange 5/27/2017 5 0 6/10/2017 7 0 6/24/2017 8 1 7/8/2017 5 0 7/22/2017 10 0

So, where _inRange equals 1, the row should be highlighted. I can highlight the _inRange cell properly, but am having difficulties highlighting the entire row. Can anyone provide direction here? I looked at this Exceljet post but obviously was left with questions.

Edit:

I changed _inRange to boolean (I don't think boolean TRUE/FALSE versus 1-0 is the main issue, though) and am still having issues:

Date    Value   _inRange
5/27/2017   5   FALSE
6/10/2017   7   FALSE
6/24/2017   8   TRUE
7/8/2017    5   FALSE
7/22/2017   10  FALSE

Here's a picture of my conditional formatting window:

I first select the entire table, then go to Home > Conditional Formatting > Highlight Cells Rules > Equal To and set the value as TRUE. Then I change the column as E. Trauger suggested but nothing changes.

ERT · Accepted Answer · 2017-07-06 14:16:59Z

1

In the Conditional Formatting Rules Manager (Home->Conditional Formatting->Manage Rules...) make sure your conditional format is correct in the "Rule" Column and then make sure the "Applies to" column is also correct. I suspect this is your problem.

Try changing the rule to: =$C2=1 And change range of application to: =$A$2:$C$10 (or whatever the C column goes to)

answered Jul 6, 2017 at 14:16

ERT

7395 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ERT Over a year ago

Your rule should be =$C2=TRUE and your "Applies to" column should be =$A$2:$C$6

Robert Redelmeier · Accepted Answer · 2017-07-06 14:16:33Z

0

The way I would do this would be to use the conditional formatting on the entire row, where for any cell in that row, it is going to check the _inRange of that row. Something like this, after New Formatting Rule --> Use a Formula to Determine which cells to format:

=INDEX(C:C,ROW())=1

Replace C:C with whatever column _inRange is in, and apply the formatting to the entire table.

answered Jul 6, 2017 at 14:16

Robert Redelmeier

636 bronze badges

1 Comment

Robert Redelmeier Over a year ago

E. Trauger's answer seems to be a much simpler and better answer for this.

Collectives™ on Stack Overflow

Conditional formatting: highlighting rows based on condition

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related