display values in cell

  • Thread starter Thread starter tim
  • Start date Start date
T

tim

I am new to Excel VBA but quite comfortable with Access
VBA.

I would like to enter a value in cell C1 based on the
following conditions

If the value in A1 is < 90 then display OK in B1
If the value in A1 is > 100 display Overdue in B1
If the value in A1 is between 90 and 100 print Notice in
B1

This is what I have but it is not working

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)

Dim celA1 As Integer
Dim celB1 As String

celA1 = Range("A1:A1")
celB1 = Range("B1:B1")


If celA1 < 90 Then
celB1 = "OK"
ElseIf celA1 > 100 Then
celB1 = "Overdue"
Else
celB1 = "Notice"
End If



End Sub

Thanks for any help
 
You've got the structure right, but are struggling with the Range object.

I figure you'll be able to get it going with your If, ElseIf routine, but
thought I'd demonstrate another way:

Sub test()
Dim rng1 As Range, rng2 As Range

Set rng1 = Range("A1")
Set rng2 = Range("B1")

Select Case rng1.Value
Case Is < 90: rng2.Value = "OK"
Case Is > 100: rng2.Value = "Overdue"
Case Else: rng2.Value = "Notice"
End Select
End Sub
 
Hi Tim
first why are you using VBA for this task. You can put the following
formula in B1
=IF(A1<>"",IF(A1>100,"OVERDUE",IF(A1<90,"OK","Notice")),"")

For your code example, try the following (if you really want to use the
worksheet change event):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim b_rng as range
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
Set b_rng = Range("B1")
On Error GoTo CleanUp:
With Target
if .value <>"" then
Application.EnableEvents = False
If .Value > 100 then
b_rng.value = "Overdue
elseif .value < 90 then
b_rng.value = "OK"
else
b_rng.value = "Notice"
end if
else
b_rng.value = ""
end if
End With
CleanUp:
Application.EnableEvents = True
End Sub



Frank
 
Frank;

Thanks, I am using VBA because this would give me more
flexibility such as writing function, sub procedure.

My Access VBA background has skewed me towards this
direction.

Thanks again

Tim

I do know that I could do what you said but my Access VBA
 
Back
Top