+ Reply to Thread
Results 1 to 2 of 2

Comparison of x and y values with other x ad y values within a tolerance

  1. #1
    Registered User
    Join Date
    07-18-2023
    Location
    United states
    MS-Off Ver
    MS 365
    Posts
    1

    Question Comparison of x and y values with other x ad y values within a tolerance

    Hello!

    I am currently trying to compare my C6 and D6 values with other values (for example C7:D7,C8:D8, etc.) but highlight the combos that are within a 0.5 tolerance of C and D value. I currently have not found anything or similar that could do such a comparison to help me.

    Maybe it is easier to check for the tolerance in column C and then check column D and compare the row number?

    Here are my example values:
    comparison example.png

    My Code i currently have is very confusing and not doing what I want.
    Sub HighlightMatchingValuesWithinOne()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastColumn As Long
    Dim tolerance As Double
    tolerance = 0.5 ' Adjust the tolerance as needed

    ' Set the worksheet and range where you want to search for matching values
    Set ws = ThisWorkbook.Sheets("Modular set") ' Change "Sheet1" to your sheet name
    Set rng = ws.Range("C6:D10") ' Adjust the range as needed

    ' Clear previous formatting (if any)
    'rng.Interior.ColorIndex = xlNone

    ' Loop through each row in the range
    For Each rw In rng.Rows
    ' Loop through each cell in the row
    For Each cell1 In rw.Cells
    ' Get the value of the current cell
    Dim cell1Value As Double
    cell1Value = cell1.Value
    'Debug.Print cell1Value


    ' Check if the value in the current cell is numeric and not empty
    If IsNumeric(cell1Value) Then
    ' Loop through each cell again to compare with other cells in the row
    For Each cell2 In rw.Cells
    ' Get the value of the other cell
    Dim cell2Value As Double
    cell2Value = cell2.Value
    'Debug.Print cell2Value

    ' Check if the other cell's value is numeric and not empty and not the same as the current cell
    If IsNumeric(cell2Value) And cell1.Address <> cell2.Address Then
    ' Check if the absolute difference between the two values is within the tolerance
    If Abs(cell1Value - cell2Value) <= tolerance Then
    ' If the values are within the tolerance, highlight both cells
    cell1.Interior.Color = RGB(255, 0, 0) ' Change the color as needed
    cell2.Interior.Color = RGB(255, 0, 0) ' Change the color as needed
    End If
    End If
    Next cell2
    End If
    Next cell1
    Next rw
    End Sub

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

    Re: Comparison of x and y values with other x ad y values within a tolerance

    Hello slink110 and Welcome to Excel Forum.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file follow the instructions in the "HOW TO ATTACH YOUR SAMPLE WORKBOOK" banner at the top of the page.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Difference between X and Y Values with Tolerance Built In
    By rtexf in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 08-11-2021, 05:26 AM
  2. Replies: 12
    Last Post: 08-06-2018, 08:27 AM
  3. [SOLVED] Need a button to search for out of tolerance values
    By Stryfe in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 05-13-2017, 05:04 AM
  4. [SOLVED] Formula or VBA - Comparison CSV Values between 3 cells for used range. Find unique values.
    By grphillips in forum Excel Programming / VBA / Macros
    Replies: 36
    Last Post: 06-09-2014, 02:12 PM
  5. [SOLVED] need values for tolerance
    By jlv7812 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-31-2014, 03:53 PM
  6. Checking a tolerance with two values
    By noobsaibot in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2013, 06:02 PM
  7. Determining Nominal & Tolerance Values
    By mycon73 in forum Excel General
    Replies: 10
    Last Post: 12-03-2010, 11:27 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