CODEWITHSUNDEEP
excelhighlight
boot-scootin
boot-scootin

Reputation: 12515

Conditional formatting: highlighting rows based on condition

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:

enter image description here

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.

Upvotes: 0

Views: 1065

Answers (2)

ERT
ERT

Reputation: 729

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)

Upvotes: 1

Robert Redelmeier
Robert Redelmeier

Reputation: 63

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.

Upvotes: 0

Related Questions