Hey everybody,
I am trying to apply conditional formatting to a dynamic named range but it is not working out so far.
When i apply the named range then the current range is given in the conditional formatting range formula.
But when the named range changes it does not automatically change the conditional formatting range.
Is there a way to work around this?
Thanks a lot
Jens Slofstra
When I do this, the "Applies To" range automatically changes if I add data to my dynamic named range. Are you actually typing the range name into the "Applies To" field? Or just selecting the cells?
I am typing the dynamic named range into the "Applies To" field. But if you hit apply then the named range will convert to just a range. For example =$A$1:$Z$1.
It converts to the range, but if anything causes the named range to change, the "Applies To" address will change correspondingly. I tested it. If it's not working in your file, attach the file and tell us the name of the range.
I only have one named range and it is called "planning"
If I type the Name in the 'Applies to' field, either as the name itself (for example R_dynamic1) or as the formula for the range (for example OFFSET(Sheet2!$A$1,0,0,1,COUNTA(Sheet2!$A$1:$Z$1)) ) then hit apply, it does instead seem to convert to a 'normal' range (for example $A$1:$D$1. But if I then enter data which extends the range (for example into E1 and F1) and then re-check the 'Applies to' for the CF, I see that this has changed (to $A$1:$F$1 in this example).
What doesn't work is that it doesn't reduce again. If I, using the example above, then delete the data in E1 and F1, the 'Applies to' range for CF remains the expanded $A$1:$F$1 not the correct $A$1:$D$1. I have to re-enter the named range (as name or formula) for it to be correct again. It does keep expanding though - it seems to be just reductions which cause the problem.
Edit: I just tried it with your file and named range and it seemed to work the same. So, sorry, but I can't reproduce the problem.
Last edited by Aardigspook; 09-28-2018 at 04:28 PM.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Okay what the goal is that if i type in the next row a start and and date it automatically needs to detect that the range has been updated en then output the formula.
This without needing to reapply the conditional format.
Edit: If I put a named range into the applies to field it automatically converts, so maybe it has something to do with my settings in excel?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks