How to color alternate rows for merged cells in Excel?
It is very helpful to format alternate rows with a different color in a large data for us to scan the data, but, sometimes, there may be some merged cells in your data. To highlight the rows alternately with a different color for the merged cells as below screenshot shown, how could you solve this problem in Excel?
Color alternate rows for merged cells with Conditional Formatting
Color alternate rows for merged cells with VBA code
Color alternate rows for merged cells with Conditional Formatting
In Excel, the powerful feature-Conditional Formatting can help you to achieve this task as quickly as possible, please do with the following steps:
1. Select the data range which contains the merged cells you want to color alternately, and then click Home > Conditional Formatting > New Rule, see screenshot:
2. In the popped out New Formatting Rule dialog box, do the following operations:
- Select Use a formula to determine which cells to format option from the Select a Rule Type list box;
- And then enter this formula: =MOD(COUNTA($A$2:$A2),2)=0 into the Format values where this formula is true text box.
- Note: A2 is the first cell of your data range.
3. Then, click Format button to go to the Format Cells dialog box. Under the Fill tab, choose one color you want to highlight the rows with, see screenshot:
4. Click OK > OK to close the dialogs, and the rows with the merged cells are filled with the color you specified, see screenshot:
5. Go on clicking Home > Conditional Formatting > New Rule to open the New Formatting Rule dialog box, do the following operations:
- Select Use a formula to determine which cells to format option from the Select a Rule Type list box;
- And then enter this formula: =MOD(COUNTA($A$2:$A2),2)=1 into the Format values where this formula is true text box.
- Then, click Format button to choose another color you want to highlight the rows.
- Note: A2 is the first cell of your data range.
6. And then, click OK > OK to close the dialogs, and now, you can see the selected data range has been shaded with two different colors alternately based on the merged cells as below screenshot shown:
Color alternate rows for merged cells with VBA code
Except the normal Conditional Formatting, you can also use a VBA code to apply alternative row colors for merged cells with ease.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Then, click Insert > Module, and paste the following code in the Module window.
VBA code: Color alternate rows for merged cells
Sub Kutools_AlternateColor()
'Update by ExtendOffice
Dim xRg As Range
Dim xCRg As Range
Dim xIRg As Range
Dim xC1, xC2 As Integer
Dim xR1 As Integer
Dim xCnt As Long
Dim xLColor, xDCR1, xDCR2 As Long
Set xRg = Application.InputBox("Please select the data range:", "KutoolsforExcel", "", Type:=8)
If TypeName(xRg) = "Nothing" Then Exit Sub
Set xCRg = Application.InputBox("Please select the column with the merged cells:", "KutoolsforExcel", "", Type:=8)
If TypeName(xCRg) = "Nothing" Then Exit Sub
Set xIRg = Intersect(xRg, xCRg)
If xIRg Is Nothing Then
MsgBox "the data range doesn't contain merged cells"
Exit Sub
End If
xC1 = xRg.Column
xC2 = xIRg.Column
xR1 = xRg.Row
xLColor = RGB(221, 235, 247)
xDCR1 = RGB(221, 235, 247)
xDCR2 = RGB(250, 232, 222)
xRw = 0
Do
xLColor = xDCR1 + xDCR2 - xLColor
xCnt = Cells(xRw + xR1, xC2).MergeArea.Rows.Count
Cells(xRw + xR1, xC1).Resize(xCnt, xRg.Columns.Count).Interior.Color = xLColor
xRw = xRw + xCnt
Loop While xRw < xRg.Rows.Count - 1
End Sub
3. Then, press F5 key to run this code. In the first popped out box, select the data range you want to use, see screenshot:
4. In the second prompt box, select the column with merged cells, see screenshot:
5. And then, click OK button, the rows are shaded alternately based on the merged cells as below screenshot shown:
Video: Color alternate rows for merged cells
More relative articles:
- Filter All Related Data From Merged Cells
- Supposing there is a column of merged cells in your data range, and now, you need to filter this column with merged cells to show all the rows which are related with each merged cell as following screenshots shown. In excel, the Filter feature allows you to filter only the first item which associated with the merged cells, in this article, I will talk about how to filter all related data from merged cells in Excel?
- Copy And Paste Merged Cells To Single Cells
- Normally, when you copy the merged cells and paste them into other cells, the merged cells will be pasted directly. But, you want to paste theses merged cells to single cells as following screenshot shown, so that you can deal with the data as you need. This article, I will talk about how to copy and paste merged cells to single cells.
- Clear The Contents Of Multiple Merged Cells
- If you have a large worksheet which contains multiple merged cells, and now, you want to clear all the contents of the merged cells but keep the merged cells. Normally, you may try to apply the Find and Replace function to select all merged cells first, and then press Delete key to delete the value, however, you will get a warning message "We can’t do that to a merged cell". In this case, how do you clear the values in merged cells but leave the merged cells quickly in Excel?
- Auto Number Merged Cells
- How could we fill series numbers into a list of different size merged cells in Excel? First in our mind is to drag the Autofill handle to fill the merged cells, but, in this case, we will get the following warning message, and can’t fill the merged cells.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
