If you work with large data sets in Microsoft Excel, you may find it helpful to format alternate rows with a different color. This can make it easier to scan the data and spot patterns or trends. You can use VBA code to apply alternative row colors for merged cells quite easily. In this article, I will show you how to alternate the row color for merged cells in Excel with ease. So, without having any further discussion, let’s begin.
How to Color Alternate Row for Merged Cells in Excel: Easy Steps
In the following dataset, the left-most column, Category has various product category names. There I used the Merge Cells command to merge consecutive rows. The merging of rows doesn’t have any particular pattern in row numbers. I will use this dataset to show you apply alternative the row color in Excel.
Step-1: Open Visual Basic Editor
I will use a VBA code to apply the alternative row color scheme for merged cells in Excel. To use the VBA code, you need to open the Visual Basic Editor first.
For that,
❶ Go to the Developer tab in the ribbon.
❷ Now click on the Visual Basic command in the Code group.
This will open the Visual Basic Editor straightway.
Alternatively, you can press the ALT + F11 keys to open the Visual Basic Editor.
Step-2: Insert VBA Code in a New Module
Now you need to open a new module to insert the VBA code. To open a new Module,
❶ Go to the Insert tab.
❷ Select the Module command from the drop-down list.
❸ Now copy the following VBA code.
Sub Alternate_Colors()
Dim xRw As Long
Dim xCnt As Long
Dim xColr As Long
With Range("B5").CurrentRegion
.EntireColumn.Interior.Color = xlNone
.EntireColumn.Borders.LineStyle = xlNone
.Columns.Borders(xlInsideVertical).Weight = xlThin
.Columns.Borders(xlInsideHorizontal).Weight = xlThin
.BorderAround , xlThin
xColr = RGB(233, 237, 244)
Do
xColr = RGB(233, 237, 244) + RGB(208, 216, 232) - xColr
xCnt = Cells(xRw + 5, "B").MergeArea.Rows.Count
Cells(xRw + 5, "B").Resize(xCnt, .Columns.Count).Interior.Color = xColr
xRw = xRw + xCnt
Loop While xRw < .Rows.Count - 1
End With
End Sub❹ Paste it to the newly opened module.
Step-3: Save Workbook as Macro-Enabled Workbook
To save the workbook with the VBA code,
❶ Go to the File tab in the ribbon.
❷ Then select the Save command.
Or you can press the CTRL + S keys together.
❸ Next click No in the pop-up dialog box to proceed with the VBA code.
❹ Now select Excel Macro-Enabled Workbook (*.xlsm) option in the Save as type drop-down.
Step-4: Run VBA Code
Now all you need to do is run the code. To run the VBA code,
❶ Go to the Developer tab.
❷ Select the Macros command in the Code group.
Or you can press the ALT + F8 keys to open the Macro dialog box.
❸ Next, click on the Run button in the Macro dialog box.
Thus, the VBA code will run. This code will alternate the row color for the merged cells just like in the following picture.
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
I have discussed steps to alternate row colors for merged cells in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.
