Intersect Code

[slimimi]

Hi there - does anyone know how i can change the formula below to look at specific rows instead please?

Say for example - i wanted to look at rows 5 to 24

Intersect(Rows(c.Row), Range("F:F,J:J,H:H,L:L,N;N,Q:Q,R:R,S:S")).NumberFormat = myFormat

 

[Peter_SSs]

You didn't say instead of what? A guess then is:

Intersect(Rows("5:24"), Range("F:F,J:J,H:H,L:L,N;N,Q:Q,R:R,S:S")).NumberFormat = myFormat

 

[slimimi]

thanks peter - remember this was the code you passed to me last time. I have revisited it but need to modify it a bit. Your guess was correct - thanks.

But it just doesnt seem to be working this time. I pasted (as last time) in the worksheet by right clicking on view code.

Somehow - i am still seeing cells with the wrong number format.
THis is what i have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
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("5:24"), Range("F:F,J:J,H:H,L:L,N;N,Q:Q,R:R,S:S,T:T")).NumberFormat = myFormat
Next c
Set myRange = Nothing
Application.ScreenUpdating = True
End If
End Sub

 

[Peter_SSs]

Somehow - i am still seeing cells with the wrong number format.

Perhaps some more detail, sample data or something to work with? Which cell(s) were changed and which cells have the wrong format?

The only think I have noticed in the code so far is in the long "Intersect" line you have N;N. Should this be N:N?

 

[slimimi]

Thanks peter - yes - i know its long - is there a way to shorten that?

I figured out the intersect problem. Its because there are merged cells on the sheet which seem to cause a problem. Is there a way around that please that you might know of?

Thanks in advance and all the best to you.

 

[Peter_SSs]

Its because there are merged cells on the sheet which seem to cause a problem. Is there a way around that please that you might know of?

"Unmerge the cells - they cause lots of problems, particularly in code". That seems to be a fairly common response from vba gurus on this board.

 

[slimimi]

great thanks peter. How about regarding my loooooonnnnggggg intersect line?

 

[Peter_SSs]

great thanks peter. How about regarding my loooooonnnnggggg intersect line?

The only issue I have with it is what I mentioned two posts ago ( ; instead of : )