Written by Allen Wyatt (last updated June 15, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
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 r As Range If Target.Address = "$A$1" Then Set r = Range("B1") r.EntireRow.HorizontalAlignment = xlGeneral r.Resize(1, Target).HorizontalAlignment = xlCenterAcrossSelection End If End Sub
Note that this macro is placed in the code sheet for the worksheet you want to affect. (Right-click the worksheet's tab and choose View Code.) Whenever a change is made in the worksheet, the macro automatically runs. To make it work for your needs, change "$A$1" to the address of the cell that contains how many columns you want to center across. (This needs to be a full address, including the dollar signs.) You'll also want to change "B1" to the address of the first cell at the left of the range where the centering is to occur. (This address does not require the dollar signs.)
The macro sets the value of the r Range variable to your starting cell. It then sets the alignment of all the cells in the row back to general. Finally, the size of the range is adjusted to be just as wide as you specified and the alignment of the range 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, 2016, 2019, 2021, and Excel in Microsoft 365.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Excel allows you to specify colors for the interior of cells in your worksheet. If you want those colors to be set ...
Discover MoreThe ability to center text across a range of cells has long been a staple of experienced Excel users. Here's how to ...
Discover MoreIf you want information to display on the screen using fractions instead of decimals, you're in luck. Excel provides ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments