Simply remove the braces around the \color{red}25.13274123. They are not necessary, and content wrapped in braces are not considered numbers I think, hence not aligned as such.
Also, the \multicolumn is not necessary, it is enough to wrap Value in braces. The reason, as I understand it, is that the e in Value could be considered a part of an exponential number, e.g. 3e8, and siunitx will then try to align it. To protect it from alignment, wrap it in braces. This is mentioned in the manual, section 4.6 Tabular material (the last sentence starting on page 13).
\documentclass{article}
\usepackage{siunitx}
\usepackage{xcolor}
\begin{document}
\begin{table} \centering
\caption{Table with \texttt{S} column type.}
\begin{tabular}{cS}
\hline
Symbol & {Value} \\
\hline
$\pi$ & 3.141592654 \\
$2\pi$ & 6.283185307 \\
$4\pi$ & 12.56637061 \\
$8\pi$ & \color{red}25.13274123 \\
$e$ & 2.71828182845904524 \\
$\sqrt{2}$ & 1.41421356237309505 \\
\hline
\end{tabular}
\end{table}
\end{document}
You need to tell siunitx how many digits are to the left and right of the decimal separator (alignment point). The key table-format does the trick.
Note also that here, a brace group is sufficient to escape the header text. The \multicolumn solution is only required in special cases.
\documentclass{article}
\usepackage{booktabs}
\usepackage{siunitx}
\sisetup{input-ignore={,},input-decimal-markers={.},group-separator={,}}
\begin{document}
\begin{tabular}{lS[table-format=9.1]*2{S[table-format=8.1]}}
\toprule
& & & {Natural} \\
Time unit & {Births} & {Deaths} & {increase} \\
\midrule
Year & 134,176,254 & 56,605,700 & 77,570,553 \\
Month & 11,181,355 & 4,717,142 & 6,464,213 \\
Day & 367,606 & 155,084 & 212,522 \\
Minute & 255 & 108 & 148 \\
Second & 4.3 & 1.8 & 2.5 \\
\bottomrule
\end{tabular}
\end{document}
The setting table-format=9.1 indicates that there are 9 digits before the alignment point and 1 digit after it. Similar for the other columns. I've also used *<num>{<col-spec>} notation for repeated common columns to reduce repetition.
Best Answer
You can set the width with
table-column-width. As you did not provide a compilable MWE I had to guess the packages which you might use and remove a few things, such as theCcolumns, the colours etc.