These types of threads always seem to pull out the measurebators. Unless you plan on printing a billboard sign you'll never be able to tell the difference between Canon's or Nikon's top glass, and anyone who says they can is full of skimmate.
Do not buy blind. Notice there were many posts in this thread recommending you going and holding different cameras to see which one feels better. Each camera has its own ergonomics, and you don't want to be stuck with an uncomfortable camera for any length of time. Sift through the menus on each camrea and see which one you think is personally more intuitive to your brain. I assume you meant you're going digital SLR, so while you're in the store ask the person helping you to put a card in the camera and snap a few photos. Don't pay attention to how the photo looks on the LCD, rather how the camera felt: ergonomics, shutter lag, autofocus, manual focus, etc. If you have the access, you should also find someone willing to show you Canon's software and Nikon's software, and see which one again is more intuitive to you.
As for the original question about "bang for the buck," IMO on Nikon's side it's the D200 and on Canon's side it's the 20D or 30D. However, you can't go wrong in image quality with either the Digital Rebel or Nikon's D50 or D70 if you're looking to stay a little cheaper.
Always keep in mind that these cameras are tools for the photographer. None of them will magically turn you into a great photographer on their own. What's more important than the camera choice you make is learning the fundamentals; composition, lighting, and control over your photograph come in way ahead of being able to spot the image quality differences between the major brands.
As for a macro lens, if you go Canon the 100mm macro or 180mm macro are excellent. The MP-E 65mm is a tremendous lens, but has serious limitations in aquaria photography due to its extremely shallow working distance. If you go Nikon, the 105mm or 180mm are excellent. In either case you have some third-party options as well. The Tamron 90mm and Sigma 105mm are excellent, too.
As for myself, i shoot Nikon. I like the more professional feel of the camera, the ruggedness, customer service (i have dealt with Canon in the past with film SLRs), lighting options with the SB-800s, and the list goes on. For most of my aquaria shots i use the Tamron 90mm. Now, with all that said, i will never say Nikon is superior than Canon, flex my muscles and walk away. I simply chose Nikon because when it came down to it, it was a better choice for me, and me alone.
Good luck with your purchase!
