3ds MAX was mentioned as a program required for modding Rome, what exactrly is it neded for?
changing the 3d models for the units, and some other items (buildings and vegetation)

3ds MAX also provides the best way of making unit cards if you have it, you can pose the units complete with texture and take a render of them to make the card. It is very expensive (and complicated) though.

lastly, Can I use photoshop to add/ change unit cards and unit colors to fit corect faction colors?
yes, definitely for unit cards, you can also use it to change colours on skins (the texture applied to the models) but you need the nvidia .dds plugin to open them.