Capturing highest value on each row

Hi All,

Have been using this forum and highly value the contributors for some time now, I am quite proficient at excel but VBA is a whole other kettle of fish.

So thank for all of the vicarious help to date, but I have a problem with hopefully an easy fix, the following piece of code does exactly what I need it to, but i need to find a way to continue the code to subsequent rows e.g.;
source_range = "f32"
target_range = "h32"

Here is the code;

Private Sub Worksheet_Change(ByVal Target As Range)

Const sSOURCE_RANGE = "F32"
Const sTARGET_RANGE = "H32"

Application.EnableEvents = False

If Not Application.Intersect(Target, Range(sSOURCE_RANGE)) Is Nothing Then
If Range(sSOURCE_RANGE).Value > Range(sTARGET_RANGE).Value Then
Range(sTARGET_RANGE).Value = Range(sSOURCE_RANGE).Value
End If
End If

Application.EnableEvents = True

End Sub

I am using this in a worksheet to track highest losses and give me a warning, this is how it looks with the 3.90 value the result of the existing code, i need it to work from rows 32 to 64
A2M 750 $2,435.01 $3.247 $3.805 HOLD 3.90 $2,853.75 $418.74 17.20% #NAME?
ANL 54,054 $2,019.95 $0.037 $0.044 HOLD $2,378.38 $358.43 17.74% #NAME?

Any help or advice would be much appreciated and thanks in advance

Originally Posted by AlphaFrog

Please Login or Register  to view this content.

Thanks so much for that, you are awesome, this has been bending my brain for some time now

Thanks again AlphaFrog,

Is there any reason the formula would be rounding up, if I change the (, 2) to (, 3) will it give me three decimal places thanks?

Originally Posted by bakerman2

Offset has nothing to do with rounding up.
To round to 3 decimal places change the line to

Please Login or Register  to view this content.

Hi Bakerman, thanks for the input I tried and it didn't quite work, still rounding at 2 places, I tried to alter the if target.value section as well and that broke the code, so looks like this one is still up in the air, if you have any more suggestions it would be much appreciated thanks.

Originally Posted by bakerman2

How about this.

Please Login or Register  to view this content.

Thanks again, this is now doing some strange things. All through this I have made sure I have my column formatted to currency with three decimal places, but this now seems to be dropping the format, so every time I format the row, the equation result takes the formatting with the new code.

I tried the original code and that is also doing the same rounding, which seems to be set at one decimal place across all three variants.

Originally Posted by xlnitwit

Don't forget that Round in VBA uses bankers' rounding unlike the ROUND worksheet function. If you want the same behaviour as the worksheet function, you need to use

Please Login or Register  to view this content.

Signed sealed and delivered, that has solved my issue, thanks to all of you, you have helped save me hours of pain and suffering, much appreciated!