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
Bookmarks