Yeah, the simple question is what you want. Do you want to create a cs-type map with a "real" touch but "cs-feeling" in it. Do you want a comic-look, fantasy-look or whatever. Some people just take pictures of their hometown and build a map for their lan-friends "Hey, look, this is my house"! Do you want to impress Valve or the so-called "mapping-scene" with your map or is it just a fun-project for you? Does anybody pay you 5000$ for your map or do you play around with hammer just for fun... I wouldn´t take this all too serious...
There are lots of Photoshop-Tuts for the filters and effects you can use for your textures... I´m not a big fan of these things and I like it "photo-raped" sometimes
----edit
just forgot it: If you try to find out what "style" the mod has just look at the textures and the skins (+faces) of the models very closely: In cs 1.6 I would say, 80% of a certain comic-style, 20% real-world-style. Sometimes they tried to make games more "real-world"-like but look at HL2: Still 70% comic-like-look in my opinion. Another famous comic-game is Max-Payne. And look at World of Warcraft: Pure colour-comic-shiny-style. Doom1 had a cool Dark-Big-Pixel-Fantasy-Look *lol*