I've done it a few times and I'm not that fond of heights either. There are a couple of places where there are a few drops but it isn't as scary as it might seem. There is a rocky tor just after Cofa Pike or just before (depending on which direction you do it) that has an awkward bit with a bit of a drop down.
You won't really know what it is like for you personally unless you give it a go. I avoided doing Cofa Pike for years but when I did it eventually, I quite enjoyed it. So I would say no, not as scary as it looks but others might disagree!