srikanthv2322
asked on
Formatting Excel Cells using VBA
I want to perform the below formatting controls on Excel cells using VBA,
For a particular Row from Column A to I, i want to apply a specific 'Fill Color'.
Change the name of the sheet
Apply 'Merge & Center' for a particular column.
How could i achieve this using vb.
For a particular Row from Column A to I, i want to apply a specific 'Fill Color'.
Change the name of the sheet
Apply 'Merge & Center' for a particular column.
How could i achieve this using vb.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
something like this
I suggest you use the macro recoder to get the right colour
Cheers
Dave
I suggest you use the macro recoder to get the right colour
Cheers
Dave
Sub Macro1()
With Range("A10:I10").Interior
.Pattern = xlSolid
.Color = vbYellow
' .Color = 65535
End With
ActiveSheet.Name = "test"
With Columns("D:D")
.HorizontalAlignment = xlCenter
.Merge
End With
End Sub
Sid
I'd missed your post on the refresh.
Two comments though
on part 1) It was Column A to I on the row, not the entire column
on part 3) Your usage of Selection.Merge is inconistent with With Sheets("Sheet1").Range("D6 :E6")
Cheers
Dave
I'd missed your post on the refresh.
Two comments though
on part 1) It was Column A to I on the row, not the entire column
on part 3) Your usage of Selection.Merge is inconistent with With Sheets("Sheet1").Range("D6
Cheers
Dave
>>>I'd missed your post on the refresh.
That's Ok Dave ;)
You are right.
srikanthv2322: Amended Code. I have also introduced a variable for the row. :)
Sid
That's Ok Dave ;)
You are right.
srikanthv2322: Amended Code. I have also introduced a variable for the row. :)
'~~> This will color the Row A to I in Sheet1 Red
Dim Rw As Long
Rw = 1
Sheets("Sheet1").Range("A" & Rw & ":I" & Rw).Interior.ColorIndex = 3
'~~> This will Rename "Sheet1" to "NewName"
Sheets("Sheet1").Name = "NewName"
'~~> This will merge D6 and E6
With Sheets("Sheet1").Range("D6:E6")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Merge
End With
Sid
!!.Do.Not.Use.Merged.Cells .!!
Use "Center across Selection" instead and draw cell borders or background fills to make the cells appear as one.
.Merge ' No! not good practise
.HorizontalAlignment = xlCenterAcrossSelection ' Yes! Cell integrity stays intact
Merged cells are bad, bad, bad, and cause more trouble than they are worth when you want to analyse and manipulate the worksheet further.
Unless, of course, you want to come back here because your other macros/formatting/copy paste/conditional formatting/sort/filter does not work, because of merged cells.
Just a thought.
cheers, teylyn
Use "Center across Selection" instead and draw cell borders or background fills to make the cells appear as one.
.Merge ' No! not good practise
.HorizontalAlignment = xlCenterAcrossSelection ' Yes! Cell integrity stays intact
Merged cells are bad, bad, bad, and cause more trouble than they are worth when you want to analyse and manipulate the worksheet further.
Unless, of course, you want to come back here because your other macros/formatting/copy paste/conditional formatting/sort/filter does not work, because of merged cells.
Just a thought.
cheers, teylyn
http://www.mvps.org/dmcritchie/excel/colors.htm
Sid