cell address

[Svgmassive]

I have dates entered in column c.I am trying to get the cell address between the range based on the cell the user selects(active cell).Thanks

Column c
7/4/2011
x
x
x
x
7/5/2011
x
x
x
7/6/2011
x
x
x
x
x
x

 

[Sektor]

Can you explain more in detail?

 

[Svgmassive]

Eg.the date 07/04/2011 cell address is c5 and 07/05/2011 cell address is c11
if the user selects cell let's say c6 the range should be c5-c10 if the user selects cell c11 the range should be c11 and what the cell address is before date 07/06/2011.etc. Hope that explains it

 

[Sektor]

In worksheet module:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_SelectionChange([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)

    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] rng1 [COLOR="Blue"]As[/COLOR] Range, rng2 [COLOR="Blue"]As[/COLOR] Range
    
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] Intersect(Target, Columns("C:C")) [COLOR="Blue"]Then[/COLOR]
        [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] IsDate(Target) [COLOR="Blue"]Then[/COLOR]
            [COLOR="Blue"]For[/COLOR] i = Target.Row [COLOR="Blue"]To[/COLOR] 1 [COLOR="Blue"]Step[/COLOR] -1
                [COLOR="Blue"]If[/COLOR] IsDate(Cells(i, "C")) [COLOR="Blue"]Then[/COLOR]
                    [COLOR="Blue"]Set[/COLOR] rng1 = Cells(i, "C").Offset(1, 0)
                    [COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]For[/COLOR]
                [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]Next[/COLOR]
            [COLOR="Blue"]For[/COLOR] i = Target.Row [COLOR="Blue"]To[/COLOR] Rows.Count
                [COLOR="Blue"]If[/COLOR] IsDate(Cells(i, "C")) [COLOR="Blue"]Then[/COLOR]
                    [COLOR="Blue"]Set[/COLOR] rng2 = Cells(i, "C").Offset(-1, 0)
                    [COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]For[/COLOR]
                [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]Next[/COLOR]
            [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] ((rng1 [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR]) [COLOR="Blue"]Or[/COLOR] (rng2 [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR])) [COLOR="Blue"]Then[/COLOR]
                MsgBox "Range address is: " & Range(rng1, rng2).Address
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Svgmassive]

sektor thanks a lot i took your idea and ran with it with a little modification it does exactly what i wanted i made it a public function.here's the modification.what you think.

For i = Target.Row To 1 Step -1
If IsDate(Cells(i, Col.Column)) Then
Set rng1 = Cells(i, Col.Column)
Exit For
End If
Next
it will search forward for the previous range until it finds a date
For i = rng1(2).Row To Rows.Count
If Not IsDate(Cells(i, Col.Column)) Then
Set rng2 = Cells(i, Col.Column)
Else
Exit For
End If