Snoopy's terrain mod is included in the "complete" release of the game, but standard, its included as a scenario. Select "game content" then select snoopy's mod, the rest is set up as a normal epic game.
However, if you like to see snoops terrain in a standard epic game, and maybe even other mods, then you should do as I said before: Move around files within the directory structure. Do this within the windows explorer/macintosh finder. It kind of speaks for itself in my opinion.
Snoop terrain can be found in
.../Civ III Complete/Conquests Game Data/Scenarios/Sn00pys Terrain Greener (C3C)/Art/ (... is where ever on your HD your civ3 folder is installed)
And the standard terrain graphics are here:
.../Civ III Complete/Civilization 3 Game Data/Art/
here:
.../Civ III Complete/Play the World Game Data/Art/
and here:
.../Civ III Complete/Conquests Game Data/Art/
You can see some of the files in these directories have the same name as the files in snoops terrain directory. Simply replace the original with snoops. (ensure yourself you backed everything up, in case something goes wrong)
If you do not feel comfortable messing with the game data like that, then maybe you shouldn't bother. (or maybe you should, to make yourself comfortable)
The watercolor terrain mod can be downloaded somewhere from this website, installing it works the same way though.