There are many trails and therefore many trailheads with a variety of road types leading to them. Most of the roads in Rocky Mountain National Park can be traveled by ordinary passenger cars, at least when it's not snowy. The roads in the nearby national forests vary widely. The main forest roads can be traveled by ordinary passenger car, but a more capable car will give you access to more places.
If you want to maximize your ability to access lots of different trailheads, particularly in the more remote parts of the National Forests, you want something with high ground clearance and all-wheel drive.
I've been on many remote forest roads throughout AZ, NM, UT and CO, both with passenger cars and high ground clearance all wheel drive vehicles. There is a lot you can get to with just a passenger car.
If I had to pick between high ground clearance and all-wheel drive, I'd definitely choose the high ground clearance. In pratise, one usually comes with the other though. The roughness of some of the roads is much more of a issue than getting stuck due to lack of traction. In some cases I was in a high ground clearance vehicle that had to be manually switched into all wheel drive. While I did that in a few cases, most of the time it wasn't worth the trouble and possibility of forgetting to switch back to two wheel drive when getting to a paved road.
You can go a lot of places with a ordinary car if you're careful and are willing to stop, get out, look at the road, and plan your route. I've actually re-arranged rocks in a stream bed to be able to drive over it in a passenger car.
Snow on the road totally changes the tradeoffs. Lots of back country roads are closed in winter instead of being maintained. Unless you have a snowmobile, you'll have to get out and ski or snowshoe them anyway. If you want to go as far as you can with a car in winter, definitely get chains and a vehicle that can do all wheel drive. In some areas, chains are actually required at certain times of the year or under certain conditions.