Intersect Method

[Jimmylaki]

Hello,

I have some questions on intersect code.

I am trying to make the following code put a matched in range s2:s500 when a user double clicks on the cell. I would also like this code to be in workbook so when a user open excel the code works.
For intersect code is it possible to change the code if a user clicks on cellA1 sheet1 it takes them to cella1 sheet 3? I kept getting error message when I tried to change this code and found little information on intersect method.

Here is the code I get error at matched line (i want to the code to also change the cell to normal if user clicks on cell after matched is in cell) (sorry for my english)

Private Sub Worksheet_SelectionChange(ByVal target As Range)

If target.Cells.Count > 1 Then Exit Sub

If Not Intersect(target, Range("S2:S500")) Is Nothing Then

target.Font.Name = "Marlett"

'target.ActiveCell.FormulaR1C1 = "Matched"


If target = vbNullString Then

target = "S"

Else

target = vbNullString

End If

End If

End Sub

 

[SkipVought]

Hi,

Private Sub Worksheet_SelectionChange(ByVal target As Range)

If target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(target, Range("S2:S500")) Is Nothing Then

        target.Font.Name = "Marlett"
        [b]
          target.Value = "Matched"
        [/b]
   '[b]WHY this IF block???
        If target.Value = vbNullString Then

           target = "S"

        End If

    End If

End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Jimmylaki]

Hi,

The IF line was reading cell to see if there was a value and if clicking again takes value out I thougth but now I am confused. To be honest I am not quite sure am still trying to learn vba. rgds JL

 

[SkipVought]

Exactly what is your objective? Please state it clearly without any reference to programming.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Jimmylaki]

Hello,

I have two objectives:

1/ I have an index on excel on sheet 1 in a daily file. When the user clicks on any field in the index for example AUDJPY rate they are taken to the sheet2 for example and the cell with that rate.

2/ My second object. I have a system which is downloaded onto excel. It captures trade information. I have to match broker statments (send in many forms, email. csv file, Reuters et) i want to have a column F have the intersect formula so when I double click in the cell next to our number it states matched. I want to put this code in the workbook so it fires when a user opens the file. i will also have some other codes in the workbook module to install toolbars.

thank you JL

 

[SkipVought]

For what purpose is the user taken to the rate on the other sheet?

I have a feeling that you are explaining HOW you expect to accomplish an ojective rather than WHAT your objective is. Is going to the other sheet REALLY the objective???

.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]