Forum Discussion
Conditional formatting for decimals
Hello Experts,
I am creating an excel sheet where user will have to put values with 4 decimal places. The value will turn red if it does not have four decimal places (i.e. more or less decimals places). I formatted the cells as number with four decimals and red font, so all input values become red (regardless of decimals places). Then, I applied the conditional formatting with formula =LEN($D6&"")-FIND(".",$D6&"")=4, and conditional formatted the font black, so all input values with four decimals turn black. Everything works fine till this stage.
I have an issue while I put value with four decimals places, but zero at fourth decimal place (e.g. 1.1110). The excel doesn't consider trailing zero at a decimal, and doesn't convert the value to black. I am sure I am missing something here.
Your support will be highly appreciated.
Thanks in advance!!
- You'll probably have better response if you post your Excel question in an Excel-specific forum, rather than this one, which supports Access.
- you can use this condition, where A1 is the start of the range to format:
=AND(ISERROR(LEN(A1)-FIND(".",A1))=FALSE, LEN(A1)-FIND(".",A1)<>4)- arnel_gp
Thanks for the formula! This is still not giving me the required result. Now I started getting the values with all the decimals turning black.
I want just the four decimals values turning black, even if the fourth decimal is zero.- i don't think it would be possible with Trailing 0, since Excel automatically remove them if the the cell is not formatted as Text.
