Do private colleges actually pay for tuition?

I'm going to be a senior next year and I'm applying to colleges this fall. I was wondering if when private schools like Harvard and Stanford, well, when they say that they will pay for tuition, does that actually happen? To be honest I probably have a chance at one of the "brand name" schools, but for some reason I feel like I shouldn't go to one. It just feels like one of those things that's only popular due to its name and these schools use this power to attract students. I don't think they are actually worth it and I don't know if undergraduate matters to future employers. Does it? I guess what I'm asking is if I am a motivated student planning to be just as motivated and high achieving or whatever in college, will going to a public school (granted with a competitive major) kill my chances at something great?