display values in cell
 

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

display values in cell
 

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"tim" wrote in message
...
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

display values in cell
 

Hi Tim
first why are you using VBA for this task. You can put the following
formula in B1
=IF(A1<"",IF(A1100,"OVERDUE",IF(A1<90,"OK","Noti ce")),"")

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


tim wrote:
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

display values in cell
 

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
-----Original Message-----
Hi Tim
first why are you using VBA for this task. You can put
the following
formula in B1
=IF(A1<"",IF(A1100,"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


tim wrote:
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


.