4 Replies Latest reply on Apr 25, 2018 11:42 AM by TaRan Wilson

    Color Cell Background by Date Range

    TaRan Wilson

      Please see the attached packaged workbook for reference.

       

      I have a series of devices that I am tracking their repair status (they have failed at some point and now a technician needs to go repair them). The tech has 45 days in which to make the repairs. I am wanting to color the cell background based on how close to the deadline the devices is. I have a column that is shading the way I want (see Sheet 8), but it's the "extra" column that appears at the end of a row of data (when there are no columns present). I want to shade the cell of the date according to how close, or far, it is from the deadline (LD Date). I kind of got it working on Sheet 9, but it's a bit of a hack and doesn't allow me to sort by that column - it's not really the way I want to do it.

       

      As a secondary function, I'm also wanting to have the color match a certain distance out from the deadline. Currently the date closest to the deadline is red and the date furthest is green. I would like for it to reflect anything that's only a couple of days out to be a darker red and if there's only one item on the list, and it's 45 days out, it should be the darkest green. Hopefully that makes sense. The calculated field "Due Date #s" is what currently controls this, but if you open it up, you'll see my code is a little strange. There has to be an easier way to write it (or perform it within the GUI).