Select Case question

[sharky12345]

I'm using this to try to update cells automatically but it is not doing what I need;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("D7").Value = "" Then
Select Case Range("D7").Value
Case Is > 8
Range("E7").Value = "Current"
End Select
Else
Select Case Range("D7").Value
Case Is < 1
Range("E7").Value = "Expired"
Case 1 To 8
Range("E7").Value = "Due to Expire"
End Select
End If

End Sub

The cell in E7 has Data Validation List and what I need is it to allow any value from that list if D7 is empty, (D7 contains a formula which does give "" if the source cell is empty). What is happening is that E7 is defaulting to 'Current'.

 

[mikerickson]

Try

If CStr(Range("D7").Value) <> "" Then
    Select Case Val(CStr(Range("D7").Value))
        Case Is < 1 Then
            Range("E7").Value = "Expired"
        Case Is < 8 Then
            Range("E7").Value = "Due To Expire"
        Case Else
            Range("E7").Value = "Current"
    End Select
End If

 

[sharky12345]

Thanks Mike, but I get a syntax error with this line highlighted;

Case Is < 1 Then

 

[mikerickson]

My syntax error. Remove the THEN from each of the Case lines.

 

[sharky12345]

Perfect - many thanks!!