Can you tell me when will use CustomValidator. For eg I have Textbox that accept comments from the user. Any reason why the CustomValidator would fit in scenario and what other validators cannot do or is difficult to do?
It depends what you are trying to validate - you will need to provide more information.
The CustomValidator lets you write any code you want to perform one or more validations on the data entered into the Textbox. The other validators perform a distinct function like comparing values, checking that a value has been entered or checking that a value conforms to a regular expression.
Your question describes the exact reason to use a CustomValidator: you use it when the existing validators won't do what you need done. For instance, there is no validator that will ensure that the value in text box B is between the values in A and C. You'd have to do that in code, in a CustomValidator.
CustomValidator is meant to give you freedom to implement your own validation logic where other validators just don't do what you need to do.
E.G: When you need to do any kind of validation that doesn't actually require a user input control (such as checking if a cookie exists) or if the control you want to validate has to follow input rules that are too involved for the other validators.
One thing I've used CustomValidators for in the past was generate a list of error messages to go in a ValidationSummary control. In general, use them whenever you would want to do some custom validation that the standard set of ASP.NET validators don't cover.