Sort range if cells change

[smowen]

Is it possible to have a macro which automatically sorts a range of cells if any value in that range changes?

[VBA Noob]

See link. Look for

Update Validation List

http://www.contextures.com/excelfiles.html#DataVal

or this event macro. Right click the sheet then select view code and paste in the below

It will sort on Col A

Please Login or Register  to view this content.

VBA Noob

[smowen]

Thanks for that.

I have discovered a problem though.

The validation list is populated from values in another worksheet, and therefore the list contains formulas with cell references in.

When the value in the other worksheet changes, and the value is entered into the validation list the macro does not run.

Is there any way round this?

[mudraker]

Worksheet_Change macro is triggered only if a cells contents are changed. This does not occur in cells with formulas

Try using a Worksheet_Calculate() macro

[smowen]

If anyone could explain how this could be done with a worksheet_calculate macro I would be very grateful.

Cheers