Can you apply a dynamic range to conditional formatting?

[Werner Peens]

Hi,

I am trying to highlight two columns on a massive spreadsheet dependent on the date at the top of the column just to make it stand out, so it is easier to see when doing weekly updates.

(As this is a client document, I am not able to make use of VBA, therefore the attempt with Conditional Formatting.)

I am using the Offset formula to define the dynamic range I want to be highlighted based on the date. Now please note that everything I have done is working fine, except that as soon as I apply the CF rule, the dynamic range (in other words the offset formula) in the "Apply to" field is changed to a set range.

So the Offset formula =(OFFSET(INDIRECT(ADDRESS(6,(MATCH(E6,J6:T6,0)+9))),2,0,25,1)) is calculated and the answer =$Q$8:$Q$32 remains in the "Apply to" field, which prevents the dynamic range to change as the date is changed.

I have created and attached a basic test file.

Thank you kindly for your assistance.

 

[Sajan]

Hello Werner,
Welcome to the forum.

I am not sure I understand what you are after... Are you looking to highlight one (or more) columns based on the date shown in that column's header?

if so, then select the data range (J8:S32 in your sample file)
and apply the following formula for Conditional Formatting
=J$6=$E$6

If you are after something else, please describe the conditions you are looking to apply, and perhaps we can suggest a different way to accomplish that.

Cheers,
Sajan.

 

[Werner Peens]

Thanks Sajan!

This works brilliantly - goes to show, efficiency doesn't necessarily need to be complicated! (and I have much more to learn)

How would one go about to highlight two columns?

Kind regards
Werner

 

[Sajan]

Hi Werner,
The above approach should have highlighted multiple columns that had the same date. Are you wanting to highlight another column based on a different date in the header? If so, use the same approach as above, but change the Conditional Format formula to
=J$6=$E$7
where $E$7 is the cell with the second date value.

You can add multiple Conditional Formats to the same range of data. (You can also control the order in which they are applied.)

Cheers,
-Sajan.