Solved: IF statements in excel

[phill952000]

hi,

i want to create an if statement that does something like this;

if cells A1 and B1 have identical values entered into them then i want B1 to display ?0.00 and if the values in A1 and B1 are different then i want B1 to display the value that was enetred.

thanks alot for reading

[Bob Phillips]

You cannot have a formula and a value in a cell, they are mutually exclusive. You could use change event code to do it.



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Offset(0, -1).Value <> .Value Then

.Value = 0
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[GTO]

Greetings Phil,

I imagine this is just a start of what you are looking to do, but try this, then maybe a more complete description of the goal?
Right-Click on the sheet's tab that you want this to happen on, and click on View Code.

Paste the below in the sheet's module.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing _
Or Not Application.Intersect(Target, Range("B1")) Is Nothing Then
If Range("B1").Value = Range("A1").Value Then
Range("B1").Value = 0
Range("B1").NumberFormat = "[$?-809]#,##0.00"
Else
Range("B1").NumberFormat = "General"
End If
End If

End Sub

Hope this helps a tiny bit,

Mark

[phill952000]

hi guys,

thanks alot for your replies i have used both pieces of code to come up with a solutioin so thanks to both of you.

regards

phill952000