Private Sub Worksheet_Activate()Dim r As RangeDim rng As RangeDim rMaxRange As RangeDim maxVal As Double 'assuming numeric valuesDim rNextCellDown As Range Set rng = Range("AG4:AK4") For Each r In rng If r.Value > maxVal Then maxVal = r.Value Set rMaxRange = r End If Next r If maxVal > 0 Then 'add 50 to the next cell down Set rNextCellDown = rMaxRange.Offset(1, 0) rNextCellDown.Value = rNextCellDown.Value + 50 End IfEnd Sub

And, if you're just working one row, the Evaluate of the MATCH(MAX function should work and be pretty efficient at it:

Private Sub Worksheet_Activate()Dim r As RangeDim rng As RangeDim colMax As VariantDim rNextCellDown As Range Set rng = Range("AG4:AK4") 'find first max value column in range - this only works if the range is on one row colMax = Evaluate("=MATCH(MAX(" & rng.Address & ")," & rng.Address & ",0)") If Not IsError(colMax) Then 'add 50 to the next cell down Set rNextCellDown = rng.Cells(2, colMax) rNextCellDown.Value = rNextCellDown.Value + 50 End IfEnd Sub

Here is my final code. It does what I need it to do - however I have duplicated steps depending of the the r1.range value. I wanted to create a seperate sub-routine to call that would complete the duplicated steps - but I could not get it to do that. If you can take this exisiting code and slim it down, then that will help me greatly. That is if you have the time.

Note, with few exceptions, selecting or activating anything on the sheet is not required and slows code down, and can trigger events based on sheet activation/selection as well. I advise against it. Using With adds value when doing more than one operation against a range, and it actually speeds up processing and is sometimes easier to read, but I'm not sure using With on one statement adds much value.

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…