0

Can anyone help me figure out why this isnt working and how to fix it? The values of the variable in the immediate window show that it should be working.

For example if cell "A" contains .122 and cell J contains .121 the code doesnt work whereas if cell "A" contains .122 and cell J contains .123 it does work.

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, od As Double, nd As Double, q As Double

i = Target.Row
q = Target.Column

If Range("K" & i).Value = "Out of Service" Or q <> 10 Then Exit Sub
If IsNumeric(Range("J" & i).Value) = False Then
    Application.EnableEvents = False 'This must be on to prevent circular reference crash
        Cells(i, 10).Interior.Color = vbGreen
        Cells(i, 10).Font.Bold = True
        Range("J" & i).Value = "In Use"
    Application.EnableEvents = True
Exit Sub
End If

od = Range("A" & i).Value + 1.0001
nd = Range("J" & i).Value + 1.0001

If od = nd Then
    Application.EnableEvents = False
        Cells(i, 10).Interior.ColorIndex = 0
        Cells(i, 10).Font.Bold = False
        Range("K" & i).Value = "Good"
    Application.EnableEvents = True
Exit Sub
End If


If Round(od,4) = Round(nd - 0.001,4) Or Round(od,4) = Round(nd + 0.001,4) Then
    Application.EnableEvents = False
        Cells(i, 10).Interior.Color = vbYellow
        Cells(i, 10).Font.Bold = True
        Range("K" & i).Value = "Marginal"
    Application.EnableEvents = True

ElseIf Round(od,4) >= Round(nd + 0.002,4) Or Round(od,4) <= Round(nd - 0.002,4) Then
    Application.EnableEvents = False
        Cells(i, 10).Interior.Color = vbRed
        Cells(i, 10).Font.Bold = True
        Range("K" & i).Value = "Replace"
    Application.EnableEvents = True
End If

End Sub
Drew Berger
  • 1
  • 2
  • Also I do not need to add 1.001 to the value of the cell. I was doing this to try to solve why the IF statement wasnt always working. I was trying to make sure it was a decimal number. – Drew Berger Nov 08 '21 at 22:59
  • I tried adding the Round (od,4) after the set value section and it still isn't working. Could I try a different data type? I only need it to work to 3 decimal points. Alternatively I guess I could drop the decimal points with a trim function or something and compare as integers??? – Drew Berger Nov 08 '21 at 23:12
  • 1
    could one of your inputs be text instead of a number(even if it looks like a number)? – Glenn G Nov 08 '21 at 23:15
  • Rounding should solve it. But I'd suggest rounding in the comparison (e.g. if Round(od, places) = Round(nd - 0.001, places) – Spinner Nov 08 '21 at 23:17
  • Glenn G - No. By adding 1.001 I have eliminated that possibility – Drew Berger Nov 08 '21 at 23:17
  • Spinner I will try that. Rounding after setting the value didnt work at all. – Drew Berger Nov 08 '21 at 23:18
  • Is it possible that there are numbers after the third decimal that are not being shown, meaning that nd-.001 might be larger or smaller than od? – Glenn G Nov 08 '21 at 23:21
  • Spinner that WORKED. Rounding inside the IF took care of it. Why didn't it work to round the variable outside of the IF? Did the double data type add digits back before the IF evaluation? – Drew Berger Nov 08 '21 at 23:23
  • I edited this code to show how to solve it since the thread was closed already. Linking to the other thread will not answer your question exactly. Knowing that the Round function must happen INSIDE the IF statement is the key. – Drew Berger Nov 09 '21 at 12:28

0 Answers0