Is there any way to have one member group require manual activation but have another group not require any activation?
I could not find a way to do this using member preferences as they are global for all member groups, so I got a little creative.
I created a custom member field and called it "approved". I made it a select field and set the selections to "yes" & "no".
In the registration form for the member group that I want to manually approve, I created a hidden field that automatically sets the "approved" value for all submissions to "no".
When I receive the notification email that a new member has registered, I log into the CP, check out their member profile info that they submitted and if they are approved, I set the member field to "yes".
In any templates that this member group has access to, I placed a conditional that checks the value of "approved" whenever someone is logged in. If the value is "no", I redirect them to a page that says their account is pending. If "yes", they see the normal content.
So an unapproved member can still login, but they won't see any content until I flip the switch.
If you are not using a membership add-on, then the easiest method is to approve all incoming memberships into the default membership group, which requires not activation (I recommend at least manual activation). Similar to what @Mediapimp has done, you would track their membership option and just perform manual escalation on those with additional privileges.
But I believe some of the membership plug-ins do allow for set-ups that can handle different approval processes.