Help with Intersect Code Please

[slimimi]

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.

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

 

[erik.van.geit]

Hi,

Basically you know what range to work with but want to expand it.
Expand or shrink: Excel has both in one function: RESIZE.

Take a look in the VBA helpfiles for RESIZE.

kind regards,
Erik