Mr.Bob,

code works perfectly weel and solved my problem completely. fantastic!

Thanks a lot.

Regards,
-Thulasiram

Bob Phillips wrote:
Sub Normalise()
Dim i As Long, j As Long
Dim rng As Range

Set rng = Selection
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
With Worksheets("Sheet2").Range("A1").Cells(i, j)
.Value = 10 * rng.Cells(i, j).Value / Application.Max(rng)
If .Value = 0 Then
.Interior.ColorIndex = 6
Else
.Interior.ColorIndex = 10
End If
.Value = Round(.Value, 0)
End With
Next j
Next i
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Thulasiram" wrote in message
ups.com...
Mr. Bob,

Thanks a lot for your reply. Surprised to see such a compact code.

Couple of questions more. This should end my questions related to this
topic.

1. How to move the values to another worksheet... i.e. move the
resultant values to another sheet in the same workbook? currently the
code given by you pastes the resultant value in the same worksheet.

2. Is it possible to do conditional formatting in the given code...
(currently i use a recoded macro and it is not generic with respect to
user selection)

I would like to have a conditional formatting like:

if the value is equal to 0, then cell to colored with yellow
if the value is greater than 0 (includes values that are rounded to
zero i.e values like 0.34 represented as 0 while rounding), then cell
to colored with green.

please help.

Thanks,
Thulasiram

Bob Phillips wrote:
Sub Normalise()
Dim i As Long, j As Long
Dim rng As Range

Set rng = Selection
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j).Value =
_
Round(10 * rng.Cells(i, j).Value / Application.Max(rng),
0)
Next j
Next i
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Thulasiram" wrote in message
oups.com...
Hello people,

I would like to normalize the values selected by the user.

Algorithm for it:

1. from the selected values of the user, find the largest value
2. for each selected cell value, divide that cells value by the
maximum
value and muliply it by 10. for example, when written as a formula it
may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25);
=10*C3/MAX($B$2:$BR$25) etc...
3. Round the values.
VBA code that i currently use for rounding is

Dim Rng As Range
Set Rng = ActiveCell
Rng.Value = Application.WorksheetFunction. _
Round(Rng.Value, 0)

unfortunately this code roundly only one cell but not all the selected
cells. please rectify this issue too.

4. paste the resulting rounded values in a separate place in the same
worksheet. i.e not to overlap with the selected area.

ALL the FOUR steps come under the tag NORMALISING.

i have a command button called as "NORMALISE". i would like to know
the
VBA code(that covers the four steps in the slgorithm) to be written in
the click event.

please help.

Thanks for all help to be rendered.
Regards,
Thulasiram.