Custom Format to Align Text in a Cell

[bhenlee]

Hi Team,

Are there any custom formats i could use to center align a particular text in a cell if it is chosen in a dropdown validation (example: N/A)?

I would like "N/A" to be aligned in the center and all other choices aligned to the left.


Thanks,

bhenlee

[aydeegee]

Hi bhenlee

One way would be to use "worksheet change" routine

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("c1:c1000 ")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
'Change the cell reference above to what you need
    If Target <> "N/A" Then Exit Sub
    Application.EnableEvents = False
    Target.HorizontalAlignment = xlCenter
    Application.EnableEvents = True
End Sub

To enter this code copy it, right click on the sheet tab and go to "View code". Paste the above. Any "N/A" entry in c1:c1000 (amend as you need) will be centre justified.

Let me know if this works for you

Regards
Alastair