I presume that this sheet is not sheet Sep307 and that you are getting a date
displayed in J1 and not some error ( I got confused somewhat between J1, JI
and Ji).
Then back up in your With statement, use the .Text parameter instead of
..Value, as:
With ActiveSheet
.Name = Range("J1").Text
End With

or more simply, since you're only changing the one ActiveSheet property,
replace all 3 of those lines with:
ActiveSheet.Name = Range("J1").Text

It works for me as long as there's something in J1. I actually wrote it all
as:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not IsEmpty(Range("J1")) Then
ActiveSheet.Name = Range("J1").Text
End If
End Sub

Make sure the code is in your worksheet code module, not in a general code
module. Right-click on the sheet's name tab and choose View Code from the
list - the code should be in the code module presented to you at that time.

"Wanna Learn" wrote:

Hello
I copied the code below from a book and it only works with text. How can
I make this work with a cell formatted as a date

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.Name = .Range("J1").Value '.text ???
End With
End Sub
This is what I have
JI is a merged cell from Ji to L1-
also this the formula in JI
=Sep307!J1+IF(WEEKDAY(Sep307!J1+1)=7,3,4)

Thanks