Conditionally align centre

Microsoft Book: office excel 2003 inside...
October 2, 2009 at 22:44:27
Specs: Windows XP
How i can align centre all cells having dia character in range B15:B35. Else align left?. I want to do this in sheet1 & Sheet2 of my workbook.
e.g.
20mm dia - align centre
150mm dia - align centre
No. - align left
Union - align left.


See More: Conditionally align centre

Report •


#1
October 3, 2009 at 06:23:35
Hi,

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
            Else
            rngCell.HorizontalAlignment = xlCenter
        End If
    End If
Next
ErrHnd:
End Sub

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.

Regards


Report •

#2
October 3, 2009 at 06:45:30
Hi,

Here is some alternative code to do the same thing

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd
Dim rngIsect As Range
Dim rngCell As Range

Set rngIsect = Application.Intersect(Range("DiamAlign"), Target)

If Not rngIsect Is Nothing Then
    For Each rngCell In rngIsect
        If InStr(1, rngCell.Text, "dia") = 0 Then
            rngCell.HorizontalAlignment = xlLeft
            Else
            rngCell.HorizontalAlignment = xlCenter
        End If
    Next
End If
ErrHnd:
End Sub

This uses Intersect to create a new range object if the changed cell and your named range overlap.
If they don't, then rngIsect is not set and returns 'Nothing'

Regards


Report •

#3
October 4, 2009 at 21:12:02
Humar, more than sufficient. excellent. Thanks you very much.

Report •

Related Solutions

#4
October 5, 2009 at 04:24:07
You're welcome

Report •


Ask Question