Using answer below for Cell A1.
How can I alter the formula for as many cells as I like, say 100 cells
vertically?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then

Target.Value = Replace(Target.Value, " ", Chr(10))
End If
End Sub

Here is an example for some cells in column A:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A1:A100")) Is Nothing Then
Else
Target.Value = Replace(Target.Value, " ", Chr(10))
End If
End Sub

--
Gary''s Student - gsnu200761


"EMZAM" wrote:

Using answer below for Cell A1.
How can I alter the formula for as many cells as I like, say 100 cells
vertically?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then

Target.Value = Replace(Target.Value, " ", Chr(10))
End If
End Sub

Thanks gsnu200761.
Formula works great, meaning It now does the vertical formatting for me,
though I have to mouse click over each of the cells for excel to change the
blocks of text from running accros the cell to downwards.
Only problem is that I keep getting the same error message:-
run-type error '13'
type mismatch

Is there something wrong here?

"Gary''s Student" wrote:

Here is an example for some cells in column A:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A1:A100")) Is Nothing Then
Else
Target.Value = Replace(Target.Value, " ", Chr(10))
End If
End Sub

--
Gary''s Student - gsnu200761


"EMZAM" wrote:

Using answer below for Cell A1.
How can I alter the formula for as many cells as I like, say 100 cells
vertically?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then

Target.Value = Replace(Target.Value, " ", Chr(10))
End If
End Sub