Hi Jesmin

From Excel help:
# displays only significant digits and does not display insignificant zeros.
0 (zero) displays insignificant zeros if a number has fewer digits than
there are zeros in the format

So for your data it should make no difference. If you used the format
00.0 then 4.5456 would display as 04.5. While if you used #0.0 as the
format it would display as 4.5.

I am not completely clear on what you are doing but if you are just
wanting to set the format of the max and min values in rng1 then I would
probably approach it differently. I would use conditional formatting to
do the formatting which means that if any of the values in the range
change then the formatting will also automatically change. E.G.

'--------------------------------------------
Dim rng1 As Range
Dim hpc As Double
Dim hpcStr As String
Dim fndCell As Range
Set rng1 = _
Application.Union(Range("E" & sr & ":" & "E" & num) _
, Range("G" & sr & ":" & "G" & num))

With rng1
.FormulaR1C1 = "=(RC[-1]-RC[-3])*100/(RC[-3])"
.NumberFormat = "#,##0.0000"
.Name = "TheRange"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RC=MIN(TheRange)"
.FormatConditions(1).Interior.ColorIndex = 36
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RC=MAX(TheRange)"
.FormatConditions(2).Interior.ColorIndex = 40
End With
'-----------------------------------------------------------------


Hope this helps
Rowan

jesmin wrote:
Hi Rowan: Thanks for the help. Now it looks finally OK. I am exhausted.
What is the diff between format "0.0" and "#0.0".
My data are like 5.1566,-5.1456,0.9,-1.2 etc. Both way, I am getting
right format.

Thanks again