Automatically fill and size cell/column

[jsavoor]

Hi

I have seen several snippets to change a series of columns to autofit using the Worksheet_Change method.

What I am looking to do is the following:

1. I have a spreadsheet that records changes
2. Columns J:Z are used to record the changes.
3. When the user enters a value in any cell within that column range the following should happen...
   1. the column width should change to 40
   2. the font of the text in that cell should change to 9pt
   3. the current date and time + a colon + space should be added to the beginning of the text in the cell as soon as the cursor moves out.
   4. So it should look like... 10/04/2013 11:00AM: blah blah blah

Could someone please assist in a macro that does this?

Thanks in advance for any help.

Jay

 

[John_w]

Try this code in the sheet's module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Columns("J:Z"), Target) Is Nothing And Target.Cells.Count = 1 Then
        Columns(Target.Column).ColumnWidth = 40
        Target.Font.Size = 9
        Application.EnableEvents = False
        Target.Value = Format(Now, "dd/mm/yyyy Hh:NnAM/PM") & ": " & Target.Value
        Application.EnableEvents = True
    End If
End Sub

 

[jsavoor]

John, I am so grateful to. Works beautifully.

Have a great weekend.

warm regds. Jay

Try this code in the sheet's module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Columns("J:Z"), Target) Is Nothing And Target.Cells.Count = 1 Then
        Columns(Target.Column).ColumnWidth = 40
        Target.Font.Size = 9
        Application.EnableEvents = False
        Target.Value = Format(Now, "dd/mm/yyyy Hh:NnAM/PM") & ": " & Target.Value
        Application.EnableEvents = True
    End If
End Sub

 

[Peter_SSs]

In case it is possible that more than one cell is changed at a time (eg Entry with Ctrl+Enter or Copy/Paste from another range) here is a variation that will cope with than scenario too.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("J:Z"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      With c
        .EntireColumn.ColumnWidth = 40
        .Font.Size = 9
        .Value = Format(Now, "dd/mm/yyyy hh:mmAM/PM: ") & .Value
      End With
    Next c
    Application.EnableEvents = True
  End If
End Sub