Hi there -
Please can someone help me to modify this code slightly
Peter gave me this wonderful code:
I need to modify the 'intersect' part so that it looks at cell row PLUS the cell row below aswell...
Example:
i enter AUD/USD in Cell A5
(I have prices in Row 5 and 6 that need changing).
The code i have below - only changes to myformat for the various target columns in row 5. I need to add to this code to make it Intersect Row 6 too (which is the row below the Cell Row, ie, A5).
Hope it makes sense.
Please can someone help me to modify this code slightly
Peter gave me this wonderful code:
I need to modify the 'intersect' part so that it looks at cell row PLUS the cell row below aswell...
Example:
i enter AUD/USD in Cell A5
(I have prices in Row 5 and 6 that need changing).
The code i have below - only changes to myformat for the various target columns in row 5. I need to add to this code to make it Intersect Row 6 too (which is the row below the Cell Row, ie, A5).
Hope it makes sense.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
''Code Changes Number Format between 2 decimal places and 4 decimal places
''depending on if "JPY" is detected in that row
Dim myRange As Range
Dim c As Range
Dim myFormat As String
Set myRange = Intersect(Target, Columns("A"))
If Not myRange Is Nothing Then
Application.ScreenUpdating = False
For Each c In myRange
If InStr(UCase(c.Value), "JPY") Then
myFormat = "0.00"
Else
myFormat = "0.0000"
End If
Intersect(Rows(c.Row), Range("E8:E8, E9:E9, H:H, K:K, N:N, Q:Q, T:T, X:X, Y:Y, AA:AA")).NumberFormat = myFormat
Next c
Set myRange = Nothing
Application.ScreenUpdating = True
End If
End Sub