Cell alignment centered

Hi everyone,
I have a problem with the cell alignment - the usual routine
".HorizontalAlignment = xlCenter" doesn't work. The font of the cell,
which I want to format is changed, but the alignment is not set. How can
I solve this problem? Thanks for your help, Simon

Code:

'Milestone creation
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
If Not Application.Intersect(Target, Range("D6:D300")) Is Nothing
Then
'Milestone Position
milestone_row = Target.Row
milestone_column = Cells(Target.Row, 2).Value + 11

'Milestone creation
If Target.Value = "M" And Cells(Target.Row, 2).Value > 0 Then
If Target.Value <> "" Then
Cells(milestone_row, milestone_column).Value = "u"
With Cells(milestone_row, milestone_column).Font
.Color = vbRed
.Name = "Wingdings"
.HorizontalAlignment = xlCenter
End With

Cells(milestone_row, milestone_column + 4).Value =
"Milestone: " & Cells(Target.Row, 1).Value
End If
'Milestone delete
ElseIf Cells(Target.Row, 2).Value > 0 Then
Cells(milestone_row, milestone_column + 4).Value = ""
Cells(milestone_row, milestone_column).Value = ""
With Cells(milestone_row, milestone_column).Font
.Color = vbBlack
.Name = "Arial"
.HorizontalAlignment = xlLeft
End With
End If
End If
End Sub

*** Sent via Developersdex http://www.developersdex.com ***

Simon,

The alignment property is a property of the range not of the font. So use

With Cells(milestone_row, milestone_column)
.Font.Color = vbRed
.Font.Name = "Wingdings"
.HorizontalAlignment = xlCenter
End With

and similar for the other condition

--
HTH

Bob Phillips

"Simon Fischer" <external.simon.fischer@de.bosch.com> wrote in message
news:uyS4kbdjFHA.3972@TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> I have a problem with the cell alignment - the usual routine
> ".HorizontalAlignment = xlCenter" doesn't work. The font of the cell,
> which I want to format is changed, but the alignment is not set. How can
> I solve this problem? Thanks for your help, Simon
>
> Code:
>
> 'Milestone creation
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> On Error Resume Next
> If Not Application.Intersect(Target, Range("D6:D300")) Is Nothing
> Then
> 'Milestone Position
> milestone_row = Target.Row
> milestone_column = Cells(Target.Row, 2).Value + 11
>
> 'Milestone creation
> If Target.Value = "M" And Cells(Target.Row, 2).Value > 0 Then
> If Target.Value <> "" Then
> Cells(milestone_row, milestone_column).Value = "u"
> With Cells(milestone_row, milestone_column).Font
> .Color = vbRed
> .Name = "Wingdings"
> .HorizontalAlignment = xlCenter
> End With
>
> Cells(milestone_row, milestone_column + 4).Value =
> "Milestone: " & Cells(Target.Row, 1).Value
> End If
> 'Milestone delete
> ElseIf Cells(Target.Row, 2).Value > 0 Then
> Cells(milestone_row, milestone_column + 4).Value = ""
> Cells(milestone_row, milestone_column).Value = ""
> With Cells(milestone_row, milestone_column).Font
> .Color = vbBlack
> .Name = "Arial"
> .HorizontalAlignment = xlLeft
> End With
> End If
> End If
> End Sub
>
> *** Sent via Developersdex http://www.developersdex.com ***