First, the advice you don't want to hear. Since you have a car that is good, runs well, and could easily be driven through 6 more years of college, swaping it for a "new" used car isn't going to be the smartest move you could make, particularly going with a straight transmission. More especially since you don't know how to drive a stick shift. Your Honda is worth much more to you than it is to anyone else because it is a solid ride. But to asnswer your wuestions:
www.Edmunds.com gives your Honda the following value:
without Options
1997 Honda Accord 2 Dr Special Edition Coupe
Dealer Trade-In $6,210
Private Party Sale $7,441 and
Dealer Retail $8,947.
What my daughter did when she wanted something more "exciting with a straight stick" was to keep her Pontiac Bonneville with low milage and good service and buy a 1973 VW Thing for $3 k to drive "for fun". But if ya gotta have a new ride, go to Consumer Reports and Edmunds.com and pick out something you like with a good overall reliability rating. While not all of us swear by these ratings, they are a good guide in looking for a car that will get your though another 6 years since the ratings are for people who do not generally give their cars the attention and care that Autopian members provide.
Here's how your Accord stacks up:
Used model overview
Overview: The Accord has been one of our top-rated sedans for years. While smoother and quieter than the standard four-cylinder engine, the larger V6 engines made the car more front-heavy and less nimble. Interior space is fine for four, tight for five. A wagon model was available through 1997. MAke sure to get an EX model, with standard ABS. The redesigned 1998 Accord is larger and quieter but similar overall. It is very refined for the class, especially when equipped with the 3.0-liter V6 engine.
Estimated retail prices
1997 $6,000-$8,000
Crash-test results
Good luck.