+ Reply to Thread
Results 1 to 3 of 3

Conditional formating loop to find numbers that are within x digits of each other.

  1. #1
    Registered User
    Join Date
    01-25-2013
    Location
    USA, Kentucky
    MS-Off Ver
    Office 2013
    Posts
    25

    Exclamation Conditional formating loop to find numbers that are within x digits of each other.

    Hello all!

    Busy night trying to find a solution, so let me explain what I am facing. I have a range of cells, each cell contains a number. I would like to set a conditional format that highlights two numbers the same color if they are within a given numbers of digits of each other.

    For example say I have the following numbers in a range:

    136 119 102 85 68 51 34 17 0 42 59 76 93 110 127 144 161 178 195
    220 203 186 169 152 135 118 101 84 126 143 160 177 194 211 228 245 262 279
    304 287 270 253 236 219 202 185 168 210 227 244 261 278 295 312 329 346 363

    I wanted to test to find out if there was any numbers in that range that were 8 or less digits of each other.
    What type of VBA or looped function could I use to highlight the two cells that contained numbers that were 8 or less apart red?


    Thanks for any help in advance.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Conditional formating loop to find numbers that are within x digits of each other.

    Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    01-25-2013
    Location
    USA, Kentucky
    MS-Off Ver
    Office 2013
    Posts
    25

    Re: Conditional formating loop to find numbers that are within x digits of each other.

    sample.xlsx

    SixthSense,

    Thanks for your reply.

    I'm basically trying to check the timing sequence of a Explosion. The cells would represent drilled holes that have been loaded with explosives. The numbers would represent the number milliseconds of delay before that hole was detonated.

    I have to ensure there is 8 milliseconds or greater between any two holes timing. If two holes are within 8ms of detonating from each other they need to be flagged as red or the cells filled red.

    I guess a formula could be: If(A1-A2<=7),Fill A1&A2 =Red, Else do nothing, this would need to loop to check A1 against ever cell with a number value, then again for every other cell in the range, checking against every other cell.

    Hope this helps to explain what I'm tryin to do.

+ 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. Replies: 0
    Last Post: 12-13-2012, 08:15 AM
  2. Conditional formating of numbers saved as text
    By Palabalo in forum Excel General
    Replies: 4
    Last Post: 03-07-2011, 06:48 PM
  3. Conditional Formating with even numbers
    By sher_amf in forum Excel General
    Replies: 8
    Last Post: 05-08-2009, 10:16 AM
  4. Replies: 4
    Last Post: 03-25-2009, 10:12 AM
  5. [SOLVED] Identify Numbers:Conditional formating
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 06: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