Why the row selected is displayed in negative value : Range ().value = Target.row

[Excel addicted]

Hello guys,
I have just joined this amazing place and I'm still novice in VBA . From the code below, I would like to display rows number of selected cells in cell "A4". The issue is I have the first number displayed in negative value without parenthesis. However I want to display a positive value between parenthesis.
Is something missing in the code
Thanks for your great help !

[COLOR=#111111][FONT=Roboto]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]If Not Intersect(Target, Range(" D22:I46 ")) Is Nothing Then[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]        Dim Cell As Range[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]        If Target.Count > 1 And Target.Count < 70 Then[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]        For Each Cell In Selection[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]        Range("A4").Value = Range("A4").Value & "(" & Cell.Row - 21 & ")"[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]        Next Cell[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]        Else[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]        Range("A4").Value = "(" & Target.Row - 21 & ")"[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]        End If[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]Else[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]Range("A4").ClearContents[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]End If[/FONT][/COLOR][COLOR=#111111][FONT=Roboto][/FONT][/COLOR][COLOR=#111111][FONT=Roboto]End Sub[/FONT][/COLOR]

 

[AlphaFrog]

The code loops through each cell selected. With the first cell in the loop, it puts something like (1). Excel evaluates that entry to just its' numeric negative (like a currency value).

Just format A4 as text and that should fix it.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_SelectionChange([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] Cell  [COLOR=darkblue]As[/COLOR] Range
    Range("A4").ClearContents
    Range("A4").NumberFormat = "@" [COLOR=green]'Text format[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Target, Range("D22:I46")) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Cell [COLOR=darkblue]In[/COLOR] Intersect(Target, Range("D22:I46"))
            Range("A4").Value = Range("A4").Value & "(" & Cell.Row - 21 & ")"
        [COLOR=darkblue]Next[/COLOR] Cell
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[Excel addicted]

It worked ! Thanks so much AlphaFrog