I too live in the Bay Area (East Bay), and struggle with this.
I could get flamed for this, but here goes: After 20 years going all over the Sierra, I've concluded that the most heartwrenchingly scenic and dramatic places are either Eastside and/or the Southern Sierra, and not, generally, in the north or west. Sorry, but Tahoe, Emigrant, Carson-Iceberg, and Yosemite (with the Benson loop exception) cannot hold a candle to the SEKI, the Palisades, the Ritter Range, the Silver Divide.
The problem is that all of the latter (High Sierra, east/south) spots take a long time to drive to from here, 5-8 hours.
Here's how I solve it: If I have just a 2-day weekend, I settle for the short drive, just to "get out." Desolation Wilderness; the north and northeast sections of the Tahoe Rim Trail; Emigrant Wilderness (either a lakes trip from the Gianelli Cabin trailhead, or a ridge trip south from Sonora Pass; I do not care for the other Emigrant trailheads); a trail out of Tuolumne Meadows (maybe Young Lakes) or out of Saddlebag Lake: those are my favorite 2-day-ers that fall within a short (3-4.5 hour) driving range. Wrights Lake or Horsetail Falls trailheads in Desolation are the very closest drive, and they get you into some great stuff right off the bat.
Whenever you can get at least 3-5 days, head for the High Sierra. My experience is that you can reasonably fit in up to a 6-hour drive and still have it feel like a backpack getaway. Just leave very early in the morning on Day 1 (or the night before), and hike out by 2 pm on your final day out. Here are some truly standout east and/or south routes that are still within reasonable (under 6 hours) driving range from East Bay:
1. Mineral King, fav is the 4-day Blackrock Pass/Five Lakes Basin/Sawtooth loop (SEKI)
2. Duck Lake to McGee Pass. Park car at McGee, hitch to Duck Lake (Mammoth Lakes area) and start. 3-4 days.
3. Tablelands, from trail to Pear Lake or Alta (SEKI). 3 days.
4. Mosquito Flat - hike over Mono Pass (the southern one) and get into the Recesses/Pioneer Basin. Out-n-back, or: a more ambitious trip would involve x-country into the McGee Pass area.
5. Lake Edison, catch the ferry and go up to the Silver Divide: Graveyard Lakes, Peter Pande Lake, etc.
6. You've been to Ritter Range twice, but here's a version you may not have done: From Agnew Meadows, hike the High Trail (PCT) north, but do it in late September, when the aspens are all-out colorful. Then explore Davis Lakes & Marie Lakes basins. 3-4 days.
7. Sphinx Lakes and Brewer Basin, from Kings Canyon's Roads End (5.5-hour drive). 3-4 days, incredibly ambitious (7000 feet straight uphill?), very high, must have x-country skills.
8. The Trinity Alps is a fantastic alternative to the Sierra - I think the forests there are exquisite, and you climb into the granite too. Canyon Creek trailhead is about the fastest trail to drive to in the Trinity Alps (5.5 hours from East Bay). Out-n-back, 3-4 days will get you exploring into some challenging granite.
There are some other highly scenic ones, but anything from Bishop south (like the Miter Basin area that someone here mentioned) exceeds 6 hours of driving from East Bay. Some day, you need to take the 7 hours to drive to North Fork Big Pine Creek and hike up to Palisade Glacier. (3 days.) You just have to.
Favorite books to consult are Trekking California (by Backpacker Mag), and High Sierra (by Phil Arnot). There are numerous other, practical books, but I always start with these two because they won't have you go "just anywhere" - they both hone in on reaching the most scenic places in all the Sierra. Trekking California will also give you some trip ideas outside the Sierra, such as the Lost Coast. Also, for great route advice and current conditions, google to find some forums on High Sierra backpacking.
Hope that helps.