VBA Looking up cell values problem

[tomboulter]

Hi guys just a quick one I hope. I'm having a bit of trouble with the code shown below. When I run it says the values of cells in the IF statement are empty when they do contain a value and i cant figure out why it doesn't work?

Anyway here's the code:

'PARAMETER CHECKS
'pH Level Individual
Private Sub pHIndCheck()
Dim LastRow As Long
LastRow = Worksheets("Samples").Range("C1048576").End(xlUp).Row

If txtpHLevel.Value > Worksheets("Parameters").Range("B3").Value And txtpHLevel.Value < Worksheets("Parameters").Range("B3").Value Then
Worksheets("Samples").Cells(LastRow, 3).Interior.Color = RGB(255, 0, 0)
MsgBox "Individual breach of pH Level in water sample. Take appropriate action.", vbCritical, "pH Level Breach"
End If
End Sub

 

[VoG]

You haven't assigned a value to txtpHLevel

Or should it be If range("txtpHLevel").Value

Why not just use Conditional formatting?

 

[tomboulter]

txtpHLevel contains a value that is entered by the user. I've tested it and thats not the problem its something to do with looking up the values in the cells.

 

[VoG]

The pH cannot be both above and below your set-point so the If statement will never be true. Try

If txtpHLevel.Value > Worksheets("Parameters").Range("B3").Value Or txtpHLevel.Value < Worksheets("Parameters").Range("B3").Value Then

 

[tomboulter]

Oh yeh was a mistake made when copying the code. Its actually if txtpHLevel lies between the two parameters set by the values in the cells that are referenced.

I've tried the code below and it still doesnt work. Just returns the message box even if txtpHLevel lies in between the parameters. I'm pretty certain its to do with how i'm looking up the cell values but im not sure why it doesnt work?

Private Sub pHIndCheck()
    Dim LastRow As Long
    LastRow = Worksheets("Samples").Range("C1048576").End(xlUp).Row
    
    If txtpHLevel.Value > Worksheets("Parameters").Range("B3").Value And txtpHLevel.Value < Worksheets("Parameters").Range("C3").Value Then
    Else
        Worksheets("Samples").Cells(LastRow, 3).Interior.Color = RGB(255, 0, 0)
        MsgBox "Individual breach of pH Level in water sample. Take appropriate action.", vbCritical, "pH Level Breach"
    End If
End Sub

 

[texasaswang]

If you are certain its the cell reference method, name them independently.

Private Sub pHIndCheck()
Dim LastRow As Long
Dim txtpHLevelmax
Dim txtpHLevelmin
 
LastRow = Worksheets("Samples").Range("C1048576").End(xlUp).Row
txtpHLevelmax = Worksheets("Parameters").Range("B3").Value
txtphLevelmin = Worksheets("Parameters").Range("C3").Value
If txtpHLevel.Value > txtpHLevelmax And txtpHLevel.Value < txtphLevelmin Then Worksheets("Samples").Cells(LastRow, 3).Interior.Color = RGB(255, 0, 0)
 
Else
Worksheets("Samples").Cells(LastRow, 3).Interior.Color = RGB(255, 0, 0)
MsgBox "Individual breach of pH Level in water sample. Take appropriate action.", vbCritical, "pH Level Breach"
End If
End Sub

 

[texasaswang]

The forumula after the 'Then' statement may also give you problems. What I usually do when I have multiple things I want to occur when conditions are met is send it to another sub, which contains my actions.