I am attempting to create a tracker where there is a column with the STATUS. In that cell I will put a formula where it reads cell D3 and based on the current date, the color could be one of four different. Example of what I am trying to do: Say the date in cell D3 is 1 Jun 15. Based on todays date (28 May 15) the cell fill color would be RED. The color RED would show if the suspense date is from the actual suspense date to 5 days before that date. From day 6 to day 14, the color would show as YELLOW. There would be 4 total colors including one if the current date is AFTER the suspense date (then it would show as black).

The objective would have this worksheet continually change colors based on the current date as this is a running tracker.



The conditional formatting doesn't seem to allow me 4 different colors, so haven't found a formula that would cover this. Any and all ideas would really help. Thanks all..........