Conditional formatting does not include cell alignment as far as I know.
You could use Visual Basic code triggered by the worksheet change event.
Assuming your worksheet is named "Diams", open up the visual basic editor (either Ctrl + f11 or Tools-Macros-Visual Basic Editor)
On the left is the Project Explorer. This list all open workbooks. If the explorer window is not visible select it from the View menu.
Find the name of your Workbook - it will look like VBAProject(MyWorkbook.xls)
Underneath you will see a list of worksheets and ThisWorkbook.
Double click on the "Diams" worksheet - it will start with a sheet number Sheet2(Diams)
In the code window there are two drop-downs.
From the left one select Worksheet (It shows General at first)
This brings up the default Worksheet_SelectionChange subroutine, but don't use this
From the right side drop-down select Change
A new subroutine will be started Private Sub Worksheet_Change(ByVal Target As Range)
You can delete the Worksheet_SelectionChange subroutine.
The Worksheet_Change subroutine will run automatically whenever any cell changes on your "Diams" worksheet.
Go back to the worksheet itself. Select the range of cells that contain the cells you want to align, and give it a name, using Insert-Name-Define. Name it "DiamAlign" click OK
Back in the visual basic editor
Enter the following code in the new subroutine:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd
Dim rngCell As Range
For Each rngCell In Target
If Application.Union(rngCell, Range("DiamAlign")).Address = Range("DiamAlign").Address Then
If InStr(1, rngCell.Text, "dia") = 0 Then
rngCell.HorizontalAlignment = xlLeft
rngCell.HorizontalAlignment = xlCenter
Now when text in one of the cells on your "Diams" worksheet is changed, this routine runs.
The routine loops through all changed cells and
The Application.Union tests if the changed cell is in your named range ("DiamAlign")
If a changed cell is in the named range, it is tested for the text "dia" and the text is then aligned.
You can expand this code to provide more options.
Remember that using the worksheet change event adds computing overhead as it runs every time any cell on this worksheet is changed.
You will need to put the same code (but with a different named range) into the change event for the second worksheet.