Center aligning largest number in selection by indenting
<p>I have a longstanding formatting frustration. I often do this manually but doing it manually takes forever, and there has to be a way to do this with either a VBA macro, conditional formatting or a clever number format.</p>

<p>Below is my desired result. It has the following properties:</p>

<ol>
<li>The largest number in the column (in this case the last number in the column, $103,420) is centered within the cell.</li>
<li>The largest number in the cell is not, however, center aligned, it is right indented until the value is centered.</li>
<li>All other numbers in the column are also right indented an equal amount. This is desirable because it lines up the ones place, tens place etc. in each number.</li>
<li>Negative numbers are denoted surrounded by parentheses.</li>
<li>The dollar sign is adjacent to the leftmost number.</li>
<li>Commas are included properly for numbers greater than 999.</li>
</ol>

<p>This result was achieved by:</p>

<ol>
<li>Applying the following number format:<code>$#,##0_);($#,##0)_);$0_);@_)</code></li>
<li>Manually adjusting the right indent of the cell on the largest number to determine when it is roughly centered. If more space must be on one side or the other, the larger space is left on the left side of the number.</li>
</ol>

<p><a href="https://i.stack.imgur.com/yfse1.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yfse1.jpg" alt="Desired Result"></a></p>

<p>I attempted to apply a number format similar to the one used in response to <a href="https://superuser.com/questions/571646/how-do-i-align-a-column-of-numbers-by-decimal-point-in-microsoft-excel-when-some">this question.</a>
Specifically my attempt at using this was to center align all cells using the following number format: <code>$?,??0;($?,??0);</code></p>

<p>That produces the following close but not quite result below.</p>

<p><a href="https://i.stack.imgur.com/J21or.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/J21or.jpg" alt="enter image description here"></a></p>

<p>Thoughts on how I can address this? I'm imagining a macro that identifies the largest number in the selection, gets the number of digits in that number, the font size, the width of the column, does some computation yielding the desired right indent and then applies the right indent. I'm just not sure how to do that kind of computation.</p>