Lisa,
The lower the ASA number the slower the speed of the film. What that means is more color saturation and less grain in your pictures. 200 speed film gets brighter, more vibrant colors than 400 speed. It also is less grainy which really comes into play if you want to make enlargements. However, the "slower" speed film requires more light than "faster" films.
Whether the average picture taker will notice a difference or will care is another issue. I notice the difference, but I'm something of a photo hound. On vacation I take a SLR camera with three lenses (28mm, 50mm, and a 75-205 zoom). I'll take an average of about 1.5 rolls per day, sometimes more. My wife thinks I'm crazy but we get lots of great pictures from our vacations. We've even blown up a few to 8x10 or 11x14 and mounted them. That's when the lower speed comes in handy. I usually use 200 speed film. When it's blown up to 8x10 or 11x14 it still retains it's clarity. 800 speed film will definitley look grainy when blown up that large.
I don't take a huge number of nighttime shots without a flash. If you're just taking family pictures at night using a flash you don't need 800 speed film. 800 speed film is good when you can't/don't want to use a flash (and don't have a tripod or can't use a tripod). If I know I'll be taking nightime pics without a flash (like to take a picture of the castle and main street) I'll load in a roll of 800 film. Even if I haven't used every picture on the previous roll I'll simply rewind that roll and load in the 800 film.
If you're just going to be printing 4x6 pics your pics will probably look okay even with 800 speed film. But you can see a difference by looking at the same picture taken with 200 speed and 800 speed film side by side. If most of your pics will be taken during the daylight or you'll be using the flash at night, I'd suggest going with 200 speed (or possibly 400) film. Your pictures will have more vibrant colors and if you want to blow up one or two, you can.