Solved: Quick Formatting Question - Text Alignment

[NateW]

Hi, Folks.

Really quick question...I tried going through help files, and I'm sure it's in there, but I don't seem to be searching for the right stuff.

All want to do is have a bit of code that change the alignment of a cell to center.

Here is the code I'm using - I'm looking for a statement at the end that will make the cell center aligned...

[VBA]

Sub FlagNoTripNum()
Dim flagRow As Integer

ThisWorkbook.Activate
Sheets("Gate Control").Activate

flagRow = 4

Do Until Sheets("Gate Control").Cells(flagRow, 6).Value = ""

If Sheets("Gate Control").Cells(flagRow, 1).Value = "" Then

Sheets("Gate Control").Cells(flagRow, 1).Value = "N/A"
Sheets("Gate Control").Cells(flagRow, 1).Interior.ColorIndex = 3
Sheets("Gate Control").Cells(flagRow, 1).Font.ColorIndex = 2
'ALIGN STATEMENT TO GO HERE

End If

flagRow = flagRow + 1

Loop

End Sub

[/VBA]

Thanks a ton in advance...
N.

[Bob Phillips]

[vba]

Sub FlagNoTripNum()
Dim flagRow As Long

flagRow = 4

Do While ThisWorkbook.Sheets("Gate Control").Cells(flagRow, 6).Value <> ""

With Sheets("Gate Control").Cells(flagRow, 1)
.Value = "N/A"
.Interior.ColorIndex = 3
.Font.ColorIndex = 2
.HorizontalAlignment = xlCenter
End With
flagRow = flagRow + 1
Loop

End Sub
[/vba]

[NateW]

Thanks, XLD - I'll have to modify that a bit to work with the if statement, but I think that gets me going. Do you know of any reference that lists all the text properties? It would be so helpful to have a place to just look that up, rather than bugging you guys with something so simple...

[NateW]

Hmm...that didn't work. Here's the code I used...

[VBA]

Sub FlagNoTripNum()
Dim flagRow As Integer

ThisWorkbook.Activate
Sheets("Gate Control").Activate

flagRow = 4

Do Until Sheets("Gate Control").Cells(flagRow, 6).Value = ""

If Sheets("Gate Control").Cells(flagRow, 1).Value = "" Then

Sheets("Gate Control").Cells(flagRow, 1).Value = "N/A"
Sheets("Gate Control").Cells(flagRow, 1).Interior.ColorIndex = 3
Sheets("Gate Control").Cells(flagRow, 1).Font.ColorIndex = 2
Sheets("Gate Control").Cells(flagRow, 1).HorizontalAligment = xlCenter

End If

flagRow = flagRow + 1

Loop

End Sub


[/VBA]

What did I get wrong?

[Bob Phillips]

I missed a letter

[vba]

Sub FlagNoTripNum()
Dim flagRow As Integer

flagRow = 4

With ThisWorkbook.Sheets("Gate Control")
Do While .Cells(flagRow, 6).Value <> ""

With .Cells(flagRow, 1)

If .Value = "" Then

.Value = "N/A"
.Interior.ColorIndex = 3
.Font.ColorIndex = 2
.HorizontalAlignment = xlCenter
End If
End With

flagRow = flagRow + 1
Loop
End With

End Sub
[/vba]

[NateW]

GREAT!! Thanks so much!! xld, you have helped me out a bunch of times now - I owe you a beer at least!!

Thanks again!

[Bob Phillips]

Next time I'm im Ontario ...

[NateW]

Sounds like a plan...hehe...I work for the beer industry, so I've got the goods!! Cheers!