If Then out of a VBA enviroment

Look into "Events" you can find some good help in the help files.

For example. Paste this into the SHEET module, not a standard module.
right click the sheet tab, view code. The SHEET module will be the default window to open in the VBA Editor.

Paste one of the 2 following codes. If your value in A1 Changes via manual user entry, use the 1st. If your value in A1 changes via formula, use the 2nd.

Thanks, that's great, really useful.

All this help I've had's been a real boon to getting up and running, you're all great people to help out the way you do.

Have a great weekend.

Iain.

Calculate code does not seem to work

Thanks for the advice, one example works like a dream, but the one below returns nothing, any ideas?

One drop down is set to "Worksheet" the other to "Calculate" and I am returing new figure to the cell with a value from a sum.

Private Sub Worksheet_Calculate()
If Range("A1").Value < 10 Then
Range("B1").Value = "Greater Than"
End If
End Sub

Kind regards,

Iain.

I think you need to change the < to >

I did try swapping the number higher and lower, plus changed the < to > and so on, but it does not display the text?

Kind regards,

Iain.

do you have BOTH codes in the Sheet module at the same time?

if so, it would be best to pick one or the other.

Is the value in A1 changed by manual user entry in A1, or by Formula in A1?

if by formula, use the Worksheet_Calculate code
if by user entry, use the Worksheet_Change code

if you have both entered in the Sheet's Module, one could be effecting the other. Remove the one you don't want to use.

I found that it would be good to include an else clause to change the value of the text cell when the calculated value drops to 10 or below. Otherwise, B1 will keep displaying "Greater Than".

Private Sub Worksheet_Calculate()
If Range("A1").Value > 10 Then
Range("B1").Value = "Greater Than"
Else
Range("B1").Value = ""
End If
End Sub

I was able to get a worksheet that worked with both types of events. I used a different target cell for each of the events (Calculation for one, manual change for the other).

Option Explicit

Private Sub Worksheet_Calculate()
If Range("D1").Value > 10 Then
Range("E1").Value = "Greater Than"
Else
Range("E1").Value = ""
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value > 10 Then
Range("B1").Value = "Greater Than"
ElseIf Target.Address = "$A$1" And Target.Value <= 10 Then
Range("B1").Value = "Lesser Than or Equal To"
End If
End Sub

However, it was important in the Worksheet_Change subroutine to use an ElseIf clause with conditions. Otherwise, it would go into an infinite loop. The first change to A1 would invoke this subroutine and cause a message to be written into B1. However, this is also a change to the worksheet so it would cause the subroutine to be invoked again. Using

Else Range("B1").Value = "Lesser Than or Equal To"

rather than

ElseIf Target.Address = "$A$1" And Target.Value <= 10 Then
Range("B1").Value = "Lesser Than or Equal To"

as the second clause would cause something to be written into B1 everytime and thereby cause the subroutine to be called over and over again.

OK, try this

add these lines to BOTH codes...

application.enableevents = false 'this should be first line

rest of code here...

application.enableevents = true 'this should be last line

If this doesn't help then....

Does the formula in D1 depend on the value in A1 ?

If yes, SERIOUSLY.... Pick EITHER the Change OR Calculate event. NOT both.

If you manually change A1, it will triger the calcuate event becuase the formula in D1 is dependant on A1.

so both events are being triggered when you change A1.