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" wrote in message
...
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 ***