EXCEL FORMATTING

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

I have cols of data (one would be percent change) where I
would divide. I want all of the cells to display right
aligned. I also want to change the "division by zero"
result to be changed to a "-", and centered. I can do it
when I want a dollar sign or no sign...with an if statment
to change the div by zero and the accounting format.
I want to it to occur on the percent columns as well, and
can't figure out how...the "-" never comes out centered.
I think I just need a custom format but can't seem to get
it.
Please help!
 
This may provide some food for thought,

Column A : Contains Numerators
Column B : Contains Denominators
Column D: Contains Formula : =If(Bn <> 0,An/Bn,"-") example :
=If(B7<>0,A7/B7,"-")

Then the macro to try is,
-------------------------------------
Private Sub Worksheet_Calculate()

Dim iRow As Integer

For iRow = 2 To 20
If Cells(iRow, 4).Value = "-" Then
Cells(iRow, 4).HorizontalAlignment = xlCenter
Else
Cells(iRow, 4).HorizontalAlignment = xlRight
Cells(iRow, 4).NumberFormat = "#.0%"
End If
Next iRow
End Sub
 
Wow! I think that is above me.
I had the column D part already.
There must be a way to do it with a format because the
accounting format does it exactly how I want it...I just
cannot get the percent to do it.
ANYBODY ELSE???
 
Back
Top