Dynamic conditional formatting based on cell values?

[HelpMeWithExcelPlease]

Hi all,

I think this is a conditional formatting issue, but I am unsure. To add some complexity, I have to insert a new column each week to provide historical context, so the formula cannot just reference column C vs. column D - the formula has to move with the insertion of new columns. The formatting should be based on comparing cells between two columns, but again these columns change based on the date. Below are example tables to help clarify:

02/19 Table

ID	2/05 Status	2/12 Status	Current Status
1	Open	ReTest	Closed
2	Open	Closed	Closed
3	Open	ReTest	ReOpen
4	Open	ReTest	ReTest
5	Open	ReTest	Open

<tbody>
</tbody>

02/26 Status

ID	02/05 Status	02/12 Status	02/19 Status	Current Status
1	Open	ReTest	Closed	Closed
2	Open	Closed	Closed	Closed
3	Open	ReTest	ReOpen	Closed
4	Open	ReTest	ReTest	ReOpen
5	Open	ReTest	Open	Closed

<tbody>
</tbody>

On the 02/19 Status Table, I would like to highlight the cells in the "Current Status" column if the 2/12 Status is ReTest and then the Current Status is Closed or ReOpen. Then, when I update the table on 2/26 (to the 02/26 Status Table), I need the new "Current Status" column cells to be highlighted based on those same conditions, except now I need to highlight the Current Status column cells if the 02/19 Status is ReTest and Current Status is either ReOpen or Closed.

Can this be done in Conditional formatting? Or do I need to add some VBA code? Or can I put in a formula into another column that returns a value, then highlights my "Current Status" column based on that value? Should I be using cell name to provide references for the formula?

Any assistance or direction you all can provide on this would be greatly appreciated! Thank you in advance!

 

[Marcelo Branco]

Maybe...
Assumes data in columns A:D, headers in row 1

Select D2:D6 and in Conditional Formatting use this formula
=AND(OR(D2="Closed",D2="ReOpen"),OFFSET(D2,0,-1)="Retest")
pick the format you want

M.

 

[HelpMeWithExcelPlease]

Hey Marcelo,

That worked beautifully, thank you!!! My one question is if there is a way to only highlight the cell under the current status column - utilizing the formula you provided allows me to highlight any cells that fit the status change criteria, but it highlights any cells that fit those criteria across multiple columns, not just the cells in my "Current Status" column. Is there a way I can add an additional condition to the formula to only highlight cells if they are within the column with the header "Current Status"?

Thanks!

 

[Marcelo Branco]

You are welcome. Glad to help.

About your question:
I'm afraid i'm not understanding what you're saying/asking. In my tests only the cells in the Current Status column were highlighted - provided the conditions were fulfilled, of course.

M.

 

[HelpMeWithExcelPlease]

So if my table on 02/19 looks like the below table, I want just the cells in the Current Status column to be highlighted in red. So for this example, only cells D2 and D4 would be highlighted in Red.
02/19 Table

ID	2/05 Status	2/12 Status	Current Status
1	Open	ReTest	Closed
2	Open	Closed	Closed
3	Open	ReTest	ReOpen
4	Open	ReTest	ReTest
5	Open	ReTest	Open

<tbody>
</tbody>

But then the next week I need to add in a new column to the left of my Current Status column, copy over the statuses from the old "Current Status" column to the 02/19 Status column, and input the newest statuses in Column E. Once this is done, i only want the cells in column E (my new Current Status column) to be highlighted.

02/26 Status

ID	02/05 Status	02/12 Status	02/19 Status	Current Status
1	Open (no format)	ReTest (no format)	Closed (blue fill)	Closed (blue fill)
2	Open (no format)	Closed (blue fill)	Closed (blue fill)	Closed (blue fill)
3	Open (no format)	ReTest (no format)	ReOpen (no format)	Closed (blue fill)
4	Open (no format)	ReTest (no format)	ReTest (no format)	ReOpen (red fill)
5	Open (no format)	ReTest (no format)	Open (no format)	Closed (blue fill)

<tbody>
</tbody>

I have additional formatting on this table as well - if the cell says "Closed" then it is highlighted in Blue, if it is "Open", "ReOpen", or "ReTest" then it has no formatting applied to it. So, on the 02/26 Status table I am showing, I would only want cell E5 to be highlighted in red.

Any thoughts on how to do that with the formula you provided? Am I perhaps applying the formatting to the incorrect range?

 

[Marcelo Branco]

You should copy (Current Status) and paste only values in the column to the left.
Otherwise the conditional formatting will be copied to the new column.

The same when copying/pasting the new column (02/19 Status) to Current Status.
Otherwise, the conditional formatting of the Current Status column will be erased.

I have to leave now. Hope this helps.

M.