PDA

View Full Version : Solved: Quick Formatting Question - Text Alignment



NateW
11-14-2007, 12:07 PM
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...



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



Thanks a ton in advance...
N.

Bob Phillips
11-14-2007, 12:21 PM
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

NateW
11-14-2007, 12:29 PM
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
11-14-2007, 12:33 PM
Hmm...that didn't work. Here's the code I used...



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




What did I get wrong?

Bob Phillips
11-14-2007, 02:47 PM
I missed a letter



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

NateW
11-14-2007, 02:50 PM
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
11-14-2007, 02:56 PM
Next time I'm im Ontario ...

NateW
11-14-2007, 03:10 PM
Sounds like a plan...hehe...I work for the beer industry, so I've got the goods!! Cheers!