Clear CF only in cell if Adjacent Cell has a date in it

[Craigos]

Hi There,

I have a range of cells that have 3 CF Conditions on each. They use a Red, Amber & Green fill colour when a certain date is entered.

The cells are in Column D (D4:D2000) and headed Date of Referral.

Column E is headed Date Cleared.

Is it possible that when a date is entered in Col E, the CF from the previous cell in Col D can be removed, so I am left with only the Date and no fill colour.

I am using a Sumproduct formula to count the Red, Amber & Green occurrences, so when a case is cleared, I can ensure that the count truly relefcts the cases outstanding in each coloured group.

If VBA is the only solution, then for info.... I already have a Worksheet Change Event on the sheet and would prefer it to work on the cell selection rather than a commmand button - not sure of using multiple Change Events.

Many Thanks

Craigos

 

[Jerry Sullivan]

Hi Craigos,

To eliminate the formatting of dates in Col D that don't have values in Col E in the same Row,you could add an additional CF Rule to the Cells in D4:D2000:

Formula is: =LEN($E4)>0
Format is: No Format
Make this the first rule (ahead of your other 3 CF rules)
Check the box: Stop if True

I am using a Sumproduct formula to count the Red, Amber & Green occurrences, so when a case is cleared, I can ensure that the count truly relefcts the cases outstanding in each coloured group.

I reviewed the earlier posts in which Aladin helped you with the CF and Sumproduct Formulas.

Unfortunately, eliminating the CF formatting of Cells D4:D2000 will not change the resulting Sumproduct count values, because those counts are based on the Actual Dates in Column D and not the Color of the Cells.

You could revise the Sumproduct formulas to not count rows with a value in Col E by adding
--ISBLANK($E4:$E1000)
as an additional criterion to each of the 3 formulas.

For example, change:
=SUMPRODUCT(--ISNUMBER($D4:$D1000),--(TODAY()-$D4:$D1000 <= 14))

to:
=SUMPRODUCT(--ISNUMBER($D4:$D1000),--(TODAY()-$D4:$D1000 <= 14),--ISBLANK($E4:$E1000))