VBA - Text centered, underlined and other font color

[akalien]

Hi,

I've got following VBA code in in addition I want the text to be centered, underlined and the font color RED.

Could please omebody help me out.

Many thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range) If Not (Application.Intersect(Target, Range("A1:A10")) _ Is Nothing) Then With Target If Not .HasFormula Then Application.EnableEvents = False .Value = UCase(.Value) Application.EnableEvents = True End If End With End If End Sub

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Michael M]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("A1:A10")) _
Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.HorizontalAlignment = xlCenter
.Font.Underline = True
.Font.ColorIndex = 3
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
End Sub

 

[akalien]

Perfect!!!

Many thanks

 

[Peter_SSs]

Note that both the above codes will error if more than one cell is changed at a time (eg two or more cells are selected and the Delete key is pressed, 2 or more cells are pasted into the range etc).
I would suggest you consider this more robust version

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim changed As Range, c As Range
  
  Set changed = Intersect(Target, Range("A1:A10"))
  If Not changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In changed
      With c
        If Not .HasFormula Then
          .HorizontalAlignment = xlCenter
          .Font.Underline = True
          .Font.ColorIndex = 3
          .Value = UCase(.Value)
        End If
      End With
    Next c
    Application.EnableEvents = True
  End If
End Sub