Worksheet Change Trigger - Update Another Cell

[Coeus]

Hi all,

Cant quite get the knack of this one. Not to familiar with using the worksheet macro terms yet so hopefully someone change help.

I have data in a table under two headings: (i) Status and (ii) Cost.

I would like a macro to review the status column and, if ever the status of the activecell is set as the term "INACTIVE" then the offset cell in the cost column is updated to 0.

For example:

A B
1 Status Cost
2 ACTIVE 500.00
3 INACTIVE 0.00

I imagine it would go something like this but I cannot get it to work!


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect("Table1[Status]") Is "INACTIVE" Then
Offset(0,1) = 0

End If

End Sub


Any help would be much appreciated!

Regards,

Coeus.

[JosephP]

untested but perhaps

Please Login or Register  to view this content.

[Coeus]

Amazingly fast response!

One day I hope to have the same ability

It worked like a charm thanks very much.

Pushing it now but would you mind explaining the macro lines? Give a man a fish, teach a man to fish!

Many thanks,

Coeus.

[JosephP]

ok ;-)

Please Login or Register  to view this content.

[Coeus]

Hi JosephP.

Many thanks for the details freshly copied and pasted in to anaylse.

I have one last favour to ask to make the macro perfect?

When I insert a new row, as the status cell will naturally be blank an error notices shows. I have tried some error handling such as 'If Error Resume Next' but it changes everything in the sheet to 0!

Not sure what the best approach is to handle errors within private sub macros?

I didn't expect this to happen as in the table the cell has not been 'changed' but just 'created'?


Bit confused but still a newbie so any help again would be very much appreciated!


Regards,

Coeus

[JosephP]

I'm sorry-I was being lazy before because I was on an ipad-perhaps

Please Login or Register  to view this content.