Color of Cell Equal to Color of Last Cell Above with Specific Value

[bondingfortoday]

Hello,

this is my first post.

I have the following problem:

I used conditional formatting to color-code rows with a specific value in column D.

If the value of the cell of a row in column D is "Pending", the background of that row is orange;
if the value is "Closed", the row becomes red;
if it's "Deleted", the row turns yellow;
and if it's "Transfer", the row becomes green.

What I want to happen now is the following:
If the value of the cell in column D is "Additional file", I want the row to turn either red ("Closed") or orange ("Pending") according to which one of these two values/colors was used last.

These rows should never be yellow or green.

What is the best way to achieve this?

I attached a file in which I only added the color-coding so far.

Thanks
bondingfortoday

[FDibbins]

Hi and welcome to the forum

Rather than create a series of complex formulas in CF, I suggest you create a helper column (I used H in your sample) and copy this down...
=IF(AND(D2="additional file",D3="additional file"),F2,IF(D3="additional file",D2,D3))

Then base the CF on that instead of D (you can hide the helper if you want.
If you want to build that into the CF rules, that is also an option, but Im not sure if this will cover all options. Add 2 extra rows above the table 1st, then use something like...
=IF(AND($D4="additional file",$D5="additional file"),$F4,IF($D5="additional file",$D4,$D5))="Pending"

[bondingfortoday]

Thanks for your reply and the warm welcome.

So far I created a helper column that gets assigned different values.
When the formula returns a blank ("") value, I would like that cell to copy down the previous value from the cell above.

Is it possible to put two formulas into a cell?

The first formula so far is:
=IF($D2="Closed","C",IF($D2="Pending","P",IF(OR($D2="Deleted",$D2="Transfer",$D2="Additional file"),"")))

The second one should do this:
If the first formula returns a blank value, copy value from cell above.

How could I do this?

I attached another file showing what I have done so far.

Thanks again for your help.

[FDibbins]

I added my initial formula into column G, and then added a modified version of your column H formula , into column J

I think if you base the CF on column G, youy should be good to go

[bondingfortoday]

Thank you very much. That solved my problem.

[FDibbins]

Happy to help