How do I change the colour of a cell in Excel (that's in a Table) based on the value of another cell in the same row and repeat this for each new row?

Question

I'm working on a fairly large table that has multiple data points in it that contain various dates.

The table looks roughly like this:

Case No.	Request Date 1	Response Date 1	Request Date 2	Response Date 2
1	1 Jan 2021	7 Jan 2021	10 Jan 2021	21 Jan 2021
2	5 Jan 2021	8 Jan 2021	8 Jan 2021	15 Jan 2021

The 'Request' and 'Response' Dates - in theory could - go on infinitely. All of this data sits in a named excel table called "AllData".

To help easily visualise where a response has been slower than normal (imagine a response should be within 2 working days), I've been trying to do conditional formatting where the Response Date [x] cell changes colour to red if the date within it is greater than 2 working days from the Request Date [x], where [x] is the same number (i.e. Request Date 1 and Response Date 1; and so on).

Every approach I know seems to involve having a new conditional formatting rule for each cell. My current approach is to use the WORKDAY formula combined with conditional formatting:

Example, in cell C2 (Case No. 1, Response Date 1):

'Cell Value' 'greater than' =WORKDAY(B2, 2)

But this approach means that I have to apply a new rule for each cell to replace B2 with D2, F2, B3, etc.

Is there an efficient way of approaching this? I would have thought that by using tables the conditional formatting should 'update' the cell reference, but this does not appear to be the case.

Thanks!

Answer 1

Option 1 - Use Conditional formatting in Rule Manager change the 'applies to' the column range. When viewing Conditional Formatting 'Manage Rules...' it appears that the formula is only referencing C2 and B2 however if you remove the $ (static cell or row) excel will cascade the formula down and 'applies to' your column range C2:C7 (in this case). Try it out :)

Conditional formatting Rule: =C2>B2+2 *remove $

Applies to: C2:C7 *the $ can stay, or be removed

Option 2 - Use Helper Column

Formula for days Elapsed: =C2-B2

Conditional formatting: Cell Value >=2

Updated to include answer for second part of question

Option 1 - Use helper column to show Due Date of response required and then compare the Due Date against the Response date column using a simple > than conditional format formula

Due Date column formula: =WORKDAY.INTL(A2,2,1,$E$2:$E$15)

Where:

A2 = start date (Request)

2 = days to add to start date *note this is a full 2 biz days

1 = weekend (Sat & Sun)

E2:E15 = List of holidays to account for *I used a list of Federal American holidays i found online in this example

Response Column conditional format formula: =B2>C2 Applies to B$2$:$B$8