Worsheet_Change

[albatross32]

I am trying to write a VBA code for the Worksheet_Change function.

It is a Private Sub in Sheet1.

My Target Address is $O$9

I need the code to say;

If Target Address value = "AGREED" And Sheet2 Q233 = 1 Then.........

So far I have got to;

CODE:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "$O$9" And UCase(Target.Value) = "PLACED" And Sheets("Sheet2").Range("Q233").Value = "1" Then

However with this I am now getting 'Run-time error '13' Type mismatch'

Can someone advise me what changes are required or alternatively any different code which would work

 

[RichardMGreen]

As far as I know, you don't need this bit:-
(0, 0)

The rest of the line looks OK.

 

[albatross32]

Thanks for the reply.

I am now getting 'Run type error '13' type mismatch

Any thoughts

 

[RoryA]

Separate the two tests:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "$O$9" Then
       If UCase(Target.Value) = "PLACED" And Sheets("Sheet2").Range("Q233").Value = "1" Then

 

[RichardMGreen]

Or it could be the fact that your testing for a string in the last bit.
Try changing
Sheets("Sheet2").Range("Q233").Value = "1"
to
Sheets("Sheet2").Range("Q233").Value = 1