Apply formatting to user selected range

[robdav]

Hi

I'm trying to apply the following formatting to a user selected (via Application.InputBox) range of cells.

It keeps bugging out on the "Cell.Value = Replace(Expression:=Cell.Value, Find:=".", Replace:="/")" line

I'd be grateful if someone could help please.

Thanks

Rob

Sub FormatCells()


Dim rng As Range
Dim DefaultRange As Range
Dim FormatRuleInput As String


'Determine a default range based on user's Selection
  If TypeName(Selection) = "Range" Then
    Set DefaultRange = Selection
  Else
    Set DefaultRange = ActiveCell
  End If


'Get A Cell Address From The User to Get Number Format From
  On Error Resume Next
    Set rng = Application.InputBox( _
      Title:="Highlight Cells Yellow", _
      Prompt:="Select a cell range to highlight yellow", _
      Default:=DefaultRange.Address, _
      Type:=8)
  On Error GoTo 0


'Test to ensure User Did not cancel
  If rng Is Nothing Then Exit Sub
  
'Highlight Cell Range
        For Each Cell In rng
        Cell.Offset(0, 1).Value = Right(Cell, 8)
        Cell = Mid(Cell, 1, Len(Cell) - 9)
        Cell.Value = Replace(Expression:=Cell.Value, Find:=".", Replace:="/")
        Next


End Sub

 

[Fluff]

What is the cell value when you get the error?
At a guess it doesn't have a "." in it.

 

[robdav]

What is the cell value when you get the error?
At a guess it doesn't have a "." in it.

Sample cell content:

2019.04.25 14:07:27

<tbody>
</tbody>

It works if I use it on a manual range selection.

 

[Fluff]

Try

        For Each cell In rng
        cell.Offset(0, 1).Value = Right(cell, 8)
        cell.Value = Mid(cell.Value, 1, Len(cell.Value) - 9)
        cell.Value = Replace(cell.Value, ".", "/")
        Next

 

[robdav]

Try

        For Each cell In rng
        cell.Offset(0, 1).Value = Right(cell, 8)
        cell.Value = Mid(cell.Value, 1, Len(cell.Value) - 9)
        cell.Value = Replace(cell.Value, ".", "/")
        Next

Perfect, many thanks Fluff.

 

[Fluff]

You're welcome & thanks for the feedback