Please see attached. I'm struggling to get conditional formatting to work within a table; to have 1 column's value highlighted when another column's value (for the same row) has a specific value.
A sample document is attached. Note this is just a sample. I've simplified it by putting all data on 1 worksheet, but my actual file has multiple worksheets and multiple tables.
Requirements:
- My first worksheet has Today's Date in a cell so the date automatically updates. There's other info on my version of this file on the 1st worksheet, but only Today's Date is relevant for this question.
- All data is manually entered into the tables. I'm using table formatting for several reasons such as when the user adds a new row the formatting is automatic, range references to the Table are automatic, formatting carries down, and I want all formulas to automatically carry down as well as the Conditional Formatting rules...so if the user adds row 15, the conditional formatting rules in Cell F14 would automatically carry down to Cell F15 (hoping this is possible - if not then this is a mute point and I probably need to tackle this another way).
- In the example attached, I want 4 conditional formatting possibilities applied to the value in Column F ("Due Date"), with the formatting based on the value that's in Column I (of the same row). Rules are as follows:
- Dark green if "Due > 2 weeks" (something like =I4="Due > 2 weeks")
- Light green if "Due within 2 weeks"
- Yellow if "Due within 1 week"
- Red if "Past Due"
- I do NOT want the entire row of the table highlighted in the color referenced above, I only want the Due Date value highlighted.
I have to have column I (Due Date Status) because there are other formulas that utilize this value to count records on another worksheet. So I'd like and would prefer that the logic be based on Column I to drive what color is shown in Column F.
Worksheet 2 (labeled at the top of the page)
- I can get Cell F4 to work individually. If you change this date to cause column I to update to one of the 4 possible "Due Date Status" values, the color correctly changes.
But see Worksheet 3 (also labeled at the top of the page)
- When I copy Cell L4 down and have No Due Date or the Due Date is 2/15/2019, the colors don't update.
I think I'm close, but I just can't figure out how to get this to work correctly.
Many thanks to anyone that can explain to me how to accomplish this.
Bookmarks