DinnerPlates


Hi all,

I am trying to write an IF statement that will affect two cells.
So, for example:
- In cell A1, I have the value 10
- In cell A2, I have the value 5
- In cell A3, I have the IF statement "=IF(A2<A1,"Order","Do Not Order")

Easy enough. But, my problem lies because I have a cell next to A3 with text in it that needs to be cleared once the value is false.

Does anyone know how to write this in a formula, or does it have to be done in a macro, in which case can anyone start me off on what code the macro would need

Any help with this would be very, very much appreciated!

Cheers!~



Re: IF statement

Andy Pope


Hi,

You could use a conditional formula in the B3.

=IF(A3="Do Not Order","","Display this text")

For a VBA solution this event code will update B3 when either A1 or A2 change.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("A1:A2"), Target) Is Nothing Then
Application.EnableEvents = False
If Range("A2").Value < Range("A1").Value Then
Range("B3") = "Display Text"
Else
Range("B3") = ""
End If
Application.EnableEvents = True
End If

End Sub






Re: IF statement

DinnerPlates

Thanks heaps for your answer Andy, however I didn't explain it thoroughly enough and so your solution hasn't worked... (Sorry!)

The problem is, is that I am trying to change the value of another cell using an IF Statement and I'm not sure if it is possible. I want a different cell (not the cell that the formula is written in) to go blank if the IF statement is true, otherwise I want it to stay the same. Do you think this is possible
21/05/2007
21/05/2007





Re: IF statement

Andy Pope

the vba solution changes a cell, without formula, on the event that is fired by changing either on of the 2 test cells.

So B3 content changes when either A1 or A2 changes.

The formula approach will check the value of the other formula cell, A3, in order to display something in B3