Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Aloisa Miller   on Mar 07 In MS Office Category.

  
Question Answered By: Haru Tanaka   on Mar 07

Sounds like you need some help with the VBA Object model in Excel.
There are things you can do with applications, workbooks, worksheets,
and ranges (a range can be a single cell).

You can get properties, assign values, trigger events, do stuff when
events are triggered, etc. For more information, please refer to the VBE
Help file, and view the extensive documentation on the object model.

Once you have done that, it will become clear that you want to mess
around with the attributes of a range (in this case, the cell  the cursor
is on).

A neat way to find out what commands you should use is to record a short
macro.

Put your cursor on cell B4, record a macro, and do the following:
Change the colour of the cell.
Change the width  of the cell.
Change the text  to wrapped text.

Not sure what you mean by column title  horizontal and vertical value.
But whatever you meant, do that as well.

Stop macro recording, and view what you have just recorded in the VBE.
It should look something like this:
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 26/07/2006 by paul.vermeulen
'

'
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Columns("C:C").ColumnWidth = 11.57
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

To now write your code the format will become very clear:

Dim CellColour as variant
Dim CellWidth as variant

CellColour = selection.interior.colorindex.value
CellWidth = selection.columnwidth.value

Have a go, and feel free to post the final code.

Share: 

 

This Question has 2 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on How to Use Excel Objects ? Or get search suggestion and latest updates.


Tagged: