Help with Intersect Code Please

slimimi

Well-known Member
Joined
May 27, 2008
Messages
532
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.


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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top