by Allen Wyatt
(last updated September 23, 2017)
Jean likes using the "Center Across Selection" setting on the Alignment tab of the Format Cells dialog box to center information across un-merged cells. She wonders, though, if there is a way she can use the contents of one cell to control across how many cells the centering occurs. For instance, if she has the number 4 in cell A1, then the centering would be across 4 cells (B1:E1), but if she changes it to 5 then the centering would be across 5 cells (B1:F1).
The only way this can be done is through the use of a macro. Because you might change the value in cell A1, the macro needs to run whenever you make a change to the workbook and then determine if that change was made in cell A1 or not. If so, then it can make the adjustment to the cells in the row.
Private Sub Worksheet_Change(ByVal Target As Range) Dim sWidth As String Dim sStartCell As String Dim iWidth As Integer Dim r As Range Dim sTemp As String sWidth = "$A$1" sStartCell = "$B$1" If Target.Address = sWidth Then iWidth = Range(sWidth).Value If iWidth > 1 Then sTemp = Right(sStartCell, 1) sTemp = sTemp & ":" & sTemp Range(sTemp).HorizontalAlignment = xlGeneral Set r = Range(sStartCell) Set r = r.Resize(1, iWidth) r.HorizontalAlignment = xlCenterAcrossSelection End If End If End Sub
Note that this macro is placed in the code sheet for the worksheet you want to affect. Whenever a change is made in the worksheet, the macro automatically runs. In order to make it work for you, you should change the addresses assigned to the sWidth and sStartCell variables. sWidth is set to the cell that contains how many columns you want to center across. sStartCell is set to the first cell at the left of the range where the centering is to occur.
The macro grabs whatever is in your sWidth cell and places it in the iWidth variable. If this value is greater than 1, then the centering changes take place. (It makes no sense to center across a selection that is less than 2 columns wide.) The alignment of all the cells is the row is set back to general, and then a range is defined that is just as wide as you specified, starting at the cell in sStartCell. For this range, the alignment is set to xlCenterAcrossSelection to give the desired results.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (4360) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
If you need to determine the font applied to a particular cell, you'll need to use a macro. This tip presents several ...Discover More
Need to get rid of the borders around a cell? The shortcut in this tip can make quick work of this formatting task.Discover More
If you want information to display on the screen using fractions instead of decimals, you're in luck. Excel provides ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.