Conditional format to all cells if at least one of them meets criteria

[ABSTRAKTUS]

Hi gurus,

Easy part:

Lets say we have small table with data based on time (hour by hour). Every hour a few tasks are live. One column is for tasks, another contains a dropdown with "Completed" and "In progress". If completed - task name will go green, if in progress - red.

Tricky part:

As soon as at least one task is marked as "Completed", I want all entries for that task to go green and "Completed".

Look at the dummy. It is more visible than all the text above.

Thanks guys!

[NBVC]

This conditional formula applied to D5:D29

=COUNTIFS($D$5:$D$29,$D5,$E$5:$E$29,"Completed")>0

would colour all when 1 or more are marked "Completed"

[ABSTRAKTUS]

Great! Thanks!

[ABSTRAKTUS]

Hi again!

I just discovered that this formula doesn't work on 2003 version. Any ideas why?

Thanks

[NBVC]

Because COUNTIFS was introduced in Excel 2007.

For 2003 version try:


=SUMPRODUCT(--($D$5:$D$29=$D5),--($E$5:$E$29="Completed"))>0

[ABSTRAKTUS]

Thanks mate again!