Tables are known for their vital role in Excel. One of Excel Table's prominent roles is creating alternating colors in Excel. However, did you know you can use other in-built tools in Excel to create alternating colors? This article will discuss various ways that can be used to create alternating colors in Excel.
Using the MOD and ROW Function
Here are the steps to follow while using this method:
1. Open the Excel application.
2. Open the Workbook and the worksheet you need to apply the formatting. Select all the cells in your dataset.
3. Click the Home tab on the Ribbon, and locate the Styles section. Under this section, click the Conditional Formatting drop-down button.
4. Choose the New Rule button from the menu to open the Edit Formatting Rule dialogue box.
5. In the Edit Formatting Rule dialogue box, choose the "Use a Formula to determine which cells to format" option in the "Select a Rule Type" section.
6. In the Edit Rule Description section, type this formula =MOD(ROW(),2)
7. Click the Format button to open the Format Cells dialogue box. In the dialogue box, click the Fill tab.
8. Finally, click the OK button.
Using ISEVEN and ROW Function
Steps:
1. Open the Workbook and the worksheet you need to apply the formatting. Select all the cells in your dataset.
2. Click the Home tab on the Ribbon, and locate the Styles section. Under this section, click the Conditional Formatting drop-down button.
3. Choose the New Rule button from the menu to open the Edit Formatting Rule dialogue box.
4. In the Edit Formatting Rule dialogue box, choose the "Use a Formula to determine which cells to format" option in the "Select a Rule Type" section.
5. In the Edit Rule Description section, type this formula =ISEVEN(ROW())
16. Click the Format button to open the Format Cells dialogue box. In the dialogue box, click the Fill tab.
7. Finally, click the OK button.
Using the VBA Tool
Steps to follow:
1. Open the Excel application.
2. Open the Workbook and the worksheet you need to apply the formatting. Select all the cells in your dataset.
3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.
4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
5. Type the following code in the empty module.
Sub ChangeRowColors()
Dim range As range
Dim chr As Long
Dim NoColor As Long
Dim Colored As Long
'I will Define Color as Input
NoColor = vbWhite
Colored = RGB(0, 255, 255)
'Select a range as variable
Set range = Selection
'You should select more than 1 Row
If range.Rows.Count = 1 Then Exit Sub
'Loop for Color Changing
For chr = 1 To range.Rows.Count
If chr Mod 2 = 0 Then
range.Rows(chr).Interior.Color = Colored 'Even Row
Else
range.Rows(chr).Interior.Color = NoColor 'Odd Row
End If
Next chr
End Sub
6. Finally, press the F5 button key to run the code.
