highlight cells after days then unhighlight after data is put in another cell

[Charger1]

excel.jpg


Please see attached picture. I have little knowledge of excel formulas but managed to get part of what I need done. As you can see from the pic there are several date fields. I need the column "required date" to highlight or change color once 4 days has elapsed from the date that in entered into the cell. I got this to work by selecting the column and going to conditional formatting and selecting "highlight cells less than" and entering the formula "=today()-4" Problem is that I need those to un highlight once the work order is complete. As you can see once it is complete the rest of the information is entered. The date it was finished, qty, etc... and then it is highlighted green. I tried to color over the rule and it wouldnt let me. the boxes were changed to red once 7 days had passed and I tried to change them to green with no success. I was wondering how to make the rule un highlight it once a date is entered into the "finished date column" or even a qty in the "qty" column. I also would like this formula to include only working days as well, not weekends. any help is greatly appreciated. Thanks!!

[benishiryo]

hi Charger1, you shouldn't be highlighting the whole row. takes up lots of memory. i don't know where your data starts, so i'm gonna assume from the picture it starts from row 182. highlight A182:I206 or wherever the data should end. use this as the conditional formatting formula:
=AND(TODAY()-$E182>4,$G182="",$H182="")

means that cell E has to be more than 4 days earlier than today & cell G & H has to be blank to be turned green

hope that helps. do upload the Excel file instead if it doesn't help