I put =TODAY() in A1, to use as today's date

You are already changing the row color with a CF rule, so maybe just change the cell color in col O?

This is for changing the cell yellow if the date gets within 7 days from today...
=$O4<=$A$1-7
I highlighted O2:O30 (adjust as needed) for this. See if this is giving you what you want (so far) and then see if you can add a new rule, based on thatm for red? (shout if you need help )