How to change a color of cells depending on the data(format) present it it?

[shivakumara]

Hi All,,

This is my second post in this forum and really need your help. Below is my problem..

I need change the color of the cell depending on the following conditions. (By the way the column will have names list)
1. single punctuations are allowed anywhere in name apart from first position.-->color should not change
2. If any name got consecutive punctuations --> color should change to RED.
3. Any punctuations apart from Dot(.), hyphen(-), apostrophe('), Space( ) are not allowed-->color should change to RED.

Example-
Shivakumara No Change
Shiva kumara No Change
Shiva'kumara No Change
shiva.kumara No Change
shiva kumara red
shiva''kumara red
shiva' kumara red
shiva. Kumara red
shiva.'kumara red
shiva:kumara red

Advance thanks for helping,
Shiva

[dilipandey]

HI Shiva,

Any punctuations apart from Dot(.), hyphen(-), apostrophe('), Space( ) are not allowed-->color should change to RED.

shiva kumara red

If space is allowed, then above one should not be Red.. please check. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[shivakumara]

Hi Dilipandey,,

First of all, Thanks for your response,
Yes of-course space is allowed, But two consecutive spaces are not allowed. that the reason for
shiva kumara red

Do u have any code for this ..?


Thanks,
Shiva

[dilipandey]

Okay.. Using conditional formatting & with the help of search function, we can achieve the desired result - but before that I need to see what all punctuations can be used apart from Dot(.), hyphen(-), apostrophe('), Space( ) ?

Regards,
DILIPandey
<click on below * if this helps>

[shivakumara]

Hi Dilipandey,,

As of now there are no other punctuations are allowed. (But might change in future), It is more helpful if the code is easy to edit as we need .

Thanks,
Shivakumara