hsandeep
Well-known Member
- Joined
- Dec 6, 2008
- Messages
- 1,226
- Office Version
- 2010
- Platform
- Windows
- Mobile
I need a vba which can perform below 2 actions:
Action1: Fill F2:F16 with the HIGHEST value respectively of C2:C16 when A1=1
Action2: Fill F2:F16 with the LOWEST value respectively of C2:C16 when A1=1
Note: Values of C2:C16 is constantly changing & gets updated regularly when A1=1 using real time data feeds
How to accomplish please, thanks.
I have a code but don't know whether it is correct or not
Code
Action1: Fill F2:F16 with the HIGHEST value respectively of C2:C16 when A1=1
Action2: Fill F2:F16 with the LOWEST value respectively of C2:C16 when A1=1
Note: Values of C2:C16 is constantly changing & gets updated regularly when A1=1 using real time data feeds
How to accomplish please, thanks.
Book2.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | 0 | Value | High | Low | |||||
2 | 609.20 | ||||||||
3 | 537.65 | ||||||||
4 | 458.65 | ||||||||
5 | 395.00 | ||||||||
6 | 330.00 | ||||||||
7 | 276.00 | ||||||||
8 | 222.00 | ||||||||
9 | 180.00 | ||||||||
10 | 141.95 | ||||||||
11 | 110.25 | ||||||||
12 | 87.75 | ||||||||
13 | 66.30 | ||||||||
14 | 49.90 | ||||||||
15 | 36.25 | ||||||||
16 | 28.30 | ||||||||
H51 |
I have a code but don't know whether it is correct or not
Code
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxVal As Double
Dim minVal As Double
Dim i As Integer
If Target.Address = "$A$1" Then
If Target.Value = 1 Then
'Find the highest value generated in C2
maxVal = Range("C2").Value
'Loop through C3:C16 to find the highest value generated
For i = 3 To 16
If Range("C" & i).Value > maxVal Then
maxVal = Range("C" & i).Value
End If
Next i
'Fill F2 with the highest value generated
Range("F2").Value = maxVal
'Find the lowest value in C2:C16
minVal = WorksheetFunction.Min(Range("C2:C16"))
'Fill F3:F16 with the lowest value
For i = 3 To 16
Range("F" & i).Value = minVal
Next i
End If
End If
End Sub
Last edited: