Sam, if you have a decent size memory card, try shooting in RAW instead. That way, you won't have to worry about using any of the preset WB and can change it afterwards at your leisure. It's also always better to tweak with the RAW files before hand and then converting it over to jpeg.
Also, consider trying out the aperture priority mode. It'll give you a baseline as to where to start with your existing lighting and will at least show you what kind of shutter speed is possible. I've been shooting in this mode for awhile now, and also in manual when I put one of the older lenses on the camera (both older macro lenses).
As for what aperture to shoot at, it really depends on what kind of lighting you're dealing with here. If the tank is dimly lit, you're gonna have to shoot it wide open. This unfortunately leaves you with a very small DOF, which isn't necessarily a bad thing, especially for isolating a certain fish as the subject. Stopping down will increase the DOF, but unless you have adequate lighting, the pictures will turn out very dark or even pitch black. One way to get around this is to use an off camera flash. Brings in a whole new world of possibilities, allowing you to freeze motion and also change the lighting directions (if you only have the onboard flash, then definitely considering snapping the pictures at an angle). Don't even worry about using the flash if you're doing full tank shots.
It's also dependent on which lens you will be using. Most lenses out there are at their sharpest when stopped down one to two stops.
BTW, don't worry about using the macro settings on either the D100 or the 300D. You'll get better results out of either the manual, aperture priority, or shutter priority modes.
If you're trying to freeze fish motion, it really depends on how fast the fish is moving. Most pictures of these guys will come out blurry unless you're shooting at higher shutter speeds; along the lines of 1/125 secs. or faster.
Flash. If you're considering using one of these, then invest in a diffuser of some sort. The SB-800s that I own have an omnibounce on their heads which diffuses the light down a bit (still not as well as softboxes, but does the trick).
The Tamron 90mm lens is tack sharp. Given a fair amount of lighting or using an off camera flash you can get some great images even when it's stopped down. I've been able to get pictures at f16 with the flash on half power.