+ Reply to Thread
Results 1 to 5 of 5

Conditional format to highlight cell or row of every x+nth value in a ordered sequence

  1. #1
    Registered User
    Join Date
    05-19-2020
    Location
    london
    MS-Off Ver
    16
    Posts
    2

    Conditional format to highlight cell or row of every x+nth value in a ordered sequence

    Hello All

    I have a list of 100000 numbers ordered in an ascending column
    I am trying to write a formula to highlight via conditional formatting every cell (or row containing that cell) where the numerical value is x (the starting number in the ordered sequence) plus n (a value that could be changed).
    So successive highlighted cells/rows are n higher than the last highlighted cell.

    Please see the attached example1.xls where I can achieve this for the first value of X shown (2414.44) (here cell C7)
    If I take n=12
    then the conditional formatting formula working off the cells containing a range of x+12 and x+13 (respectively cells F7 & G7) correctly highlights the next values in the x+12 range (2426.43 & 2426.87, cells C34 & C35) in red.
    However, when I try and 'drag and extend' the formatting I cannot make the formula relatively reference successive x+12 and x+13 cells (for example as shown, cells F34 & G34).

    Any help to achieve this would be appreciated - the real number list to be analysed is 100000 long and so a laborious manual task otherwise.

    Thanks in advance

    KB
    Attached Files Attached Files
    Last edited by kam2004; 05-22-2020 at 05:33 PM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,432

    Re: Conditional format to highlight cell or row of every x+nth value in a ordered sequence

    Hello kam2004 and Welcome to Excel Forum.
    Perhaps this will help.
    1. Put an "X" in column B adjacent to the first value.
    2. Use the following formula as a conditional formatting rule for column C:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    05-19-2020
    Location
    london
    MS-Off Ver
    16
    Posts
    2

    Re: Conditional format to highlight cell or row of every x+nth value in a ordered sequence

    Your solution is genius - i wouldn't have figured it this way in 1000 years!. Thank you.
    My head is hurting trying to figure out the logic in your formula - for my learning , what is the purpose of the reference to the top of the number list, here cell C3 for?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,432

    Re: Conditional format to highlight cell or row of every x+nth value in a ordered sequence

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. I have done it for you this time.)

    https://www.mrexcel.com/board/thread...value.1134932/
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,432

    Re: Conditional format to highlight cell or row of every x+nth value in a ordered sequence

    That is just the way that the formula reads. As with any relative reference it's application will change as the conditional formatting of each cell in column C is checked, although the rule reads as static.
    The question caused me to consider the case of putting an "x" in B29 therefore selecting the value 2424.24 which would cause cell C3 to highlight.
    Assuming that this is not a desired result I added a second formula: =C3<=INDEX(C:C,MATCH("X",B:B,0)) which produces no formatting.
    Note that this new rule precedes the one given in post #2 and that the Stop if True is checked.
    If this works then I hope you will apply good help forum etiquette and mark the threads on both sites as 'Solved'.
    Let us know if you have any questions.
    Attached Files Attached Files

+ 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. [SOLVED] Conditional Format Formula to Highlight Cell
    By [email protected] in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2019, 11:29 AM
  2. [SOLVED] Conditional Format to highlight blank cell
    By DaveMoore in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 12:05 PM
  3. Conditional Format to highlight blank cell
    By Mangesh Yadav in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 10:05 AM
  4. [SOLVED] Conditional Format to highlight blank cell
    By DaveMoore in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. Conditional Format to highlight blank cell
    By DaveMoore in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  6. Conditional Format to highlight blank cell
    By DaveMoore in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. Conditional Format to highlight blank cell
    By DaveMoore in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-23-2005, 05:10 AM

Tags for this Thread

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