Assert.Throws returns the exception that's thrown which lets you assert on the exception.
var ex = Assert.Throws<Exception>(() => user.MakeUserActive());
Assert.That(ex.Message, Is.EqualTo("Actual exception message"));
So if no exception is thrown, or an exception of the wrong type is thrown, the first (Assert.Throws) assertion will fail. However if an exception of the correct type is thrown then you can now assert on the actual exception that you've saved in the variable.
By using this pattern you can assert on other things than the exception message to, for example in the case of ArgumentException and derivatives you can assert that the parameter name is correct:
var ex = Assert.Throws<ArgumentNullException>(() => foo.Bar(null));
Assert.That(ex.ParamName, Is.EqualTo("bar"));
You can also use the fluent api for doing these asserts:
Assert.That(() => foo.Bar(null),
Throws.Exception.TypeOf<ArgumentNullException>().With.Property("ParamName").EqualTo("bar"));
A little tip when asserting on exception messages is to decorate the test method with the SetCultureAttribute to make sure that the thrown message is using the expected culture. This comes into play if you store your exception messages as resources to allow for localization.