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 rng.Cells(rng.Rows.Count + i, j)
.Value = 10 * rng.Cells(i, j).Value / Application.Max(rng)
If .Value = 0 Then
.Interior.ColorIndex = 6
ElseIf .Value <= 5 Then
.Interior.ColorIndex = 10
Else
.Interior.ColorIndex = 3
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
oups.com...
That was what I perfectly wanted Mr.Bob. The results are shown diagonal
to the existing workspace. Is there any possibility to show the bring
just below the workspace?

Thanks for the help rendered.

Best,
Thulasiram.

Bob Phillips wrote:
Is this what you want?

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 rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j)
.Value = 10 * rng.Cells(i, j).Value /
Application.Max(rng)
If .Value = 0 Then
.Interior.ColorIndex = 6
ElseIf .Value <= 5 Then
.Interior.ColorIndex = 10
Else
.Interior.ColorIndex = 3
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
oups.com...
Mr. Bob,

Two questions. All of them requires slight modification of the present
algorithm. I have been trying to manipulate ur code to fit my
different
conditions. But I landed into trouble.

Case 1: Code to normalise the algorithm and display the results in the
same worksheet - YOU HAVE GIVEN THE CODE in your previous reply

Case 2: Code to normalise the algorithm, display the results in the
next worsheet with conditional formatting - YOU HAVE GIVEN THE CODE in
your previous reply

**** I would like to have the code for case 2 but "results displayed
in
the same page". Algorithm, conditional formatting and rest remain the
same. The only change is that results should be in the same page.

**** I would like to have the code for case 2 but " 3 conditions for
conditional formatting". Algorithm, conditional formatting and rest
remain the same. The two changes a results is the conditional
formatting and results displayed in the same page.

Conditions a
Yellow if cell value is zero.
Green if cell value is BETWEEN 0 and 5.
Red if cell value is greater than 5.

(I tried few if.. else.. conditions in ur code for case 2.. but landed
in unpredictable error).

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
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.