+ Reply to Thread
Results 1 to 7 of 7

Conditional formatting with dynamic named ranges

  1. #1
    Registered User
    Join Date
    09-13-2016
    Location
    The Netherlands, Amsterdam
    MS-Off Ver
    2013
    Posts
    20

    Conditional formatting with dynamic named ranges

    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

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,457

    Re: Conditional formatting with dynamic named ranges

    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?
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    09-13-2016
    Location
    The Netherlands, Amsterdam
    MS-Off Ver
    2013
    Posts
    20

    Re: Conditional formatting with dynamic named ranges

    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.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,457

    Re: Conditional formatting with dynamic named ranges

    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.

  5. #5
    Registered User
    Join Date
    09-13-2016
    Location
    The Netherlands, Amsterdam
    MS-Off Ver
    2013
    Posts
    20

    Re: Conditional formatting with dynamic named ranges

    I only have one named range and it is called "planning"
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Conditional formatting with dynamic named ranges

    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

    Recently moved house, internationally, during COVID (!) 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.

  7. #7
    Registered User
    Join Date
    09-13-2016
    Location
    The Netherlands, Amsterdam
    MS-Off Ver
    2013
    Posts
    20

    Re: Conditional formatting with dynamic named ranges

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Syntax for Conditional formatting using named ranges
    By MKaprielian in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-14-2015, 12:05 AM
  2. Conditional formatting using named ranges.
    By Turtleman10 in forum Excel General
    Replies: 0
    Last Post: 05-16-2013, 08:57 AM
  3. [SOLVED] Conditional formatting with named ranges, need help!
    By petelomax in forum Excel General
    Replies: 2
    Last Post: 04-27-2012, 06:30 AM
  4. [SOLVED] Conditional Formatting with Named Ranges
    By Revverend in forum Excel General
    Replies: 7
    Last Post: 04-02-2012, 05:31 AM
  5. conditional formatting based on 5 named ranges
    By dcgrove in forum Excel General
    Replies: 8
    Last Post: 01-06-2010, 06:10 PM
  6. Conditional Sum Wizard with dynamic named ranges
    By willee in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-05-2006, 03:03 PM
  7. conditional named dynamic ranges
    By matthew701@aol.com in forum Excel General
    Replies: 1
    Last Post: 06-22-2005, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1