How to Colorize Text of Excel Cells/Rows/Cols by openpyxl

Styling Excel rows using openpyxl

You can apply various style properties to the Excel sheets while working with openpyxl library.

For example, setting the font name like Verdana, Arial, etc.

Similarly, font face like Bold and italic.

Setting the text color is also possible.

In this tutorial, we will show you a few examples of setting the text color of cells, rows, and columns with openpyxl library.

For all our examples below, we will use the following sample sheet:

An example of setting text color of a cell

Let us start with a simple example of setting the text color from default to others.

For that, we take the Cell numbers B5, C5, and D5.

To change the colors of cells, follow these steps:

Step 1:

Import Workbook and Font from openpyxl:

from openpyxl import load_workbook

from openpyxl.styles import Font

Step 2:

Load the workbook:

wb_style = load_workbook(‘test_Excel.xlsx’)

(Set the path of yours)

Step 3:

Specify active sheet to the sheet object:

sheet = wb_style.active

Step 4:

Assign cells and styles as follows:

cell_1 = sheet[“B5″]

cell_1.font = Font(color=”00FF8000”)

Note: You have to use aRGB hex values for the color.

Step 5:

Save the workbook:

wb_style.save(“test_Excel.xlsx”)

That’s it.

Following is the complete code to change three cell colors and its output:

# Color the row text example

from openpyxl import load_workbook

from openpyxl.styles import Font

#Loading the Workbook

wb_style = load_workbook('test_Excel.xlsx')

#Accessing Active Sheet

sheet = wb_style.active

cell_1 = sheet["B5"]
cell_1.font = Font(color="FF8000")

cell_2 = sheet["C5"]
cell_2.font = Font(color="800000")

cell_3 = sheet["D5"]
cell_3.font = Font(color="#0000FF")


wb_style.save("test_Excel.xlsx")

Result:

Changing the color of whole row example

Rather than changing a cell color one by one you may change the color of a complete row. For that, we will use the iter_rows function and specify the min_row and max_row as shown in the example below.

The program below will change the color of the second row – which is below the headers.

The code:

# Change color of whole row
from openpyxl import load_workbook
from openpyxl.styles import Font

#Loading the Workbook

wb_style = load_workbook('test_Excel.xlsx')

#Accessing Active Sheet
sheet = wb_style.active

for rows in sheet.iter_rows(min_row=2, max_row=2, min_col=1):
   for cell in rows:
     cell.font = Font(color="00FF00")

wb_style.save("test_Excel.xlsx")

Result:

You can see the green color for the second row.

Changing many rows color example

The example below changes the row color from 2 to 6:

Python program:

# Change color of whole row
from openpyxl import load_workbook
from openpyxl.styles import Font

#Loading the Workbook
wb_style = load_workbook('test_Excel.xlsx')

#Accessing Active Sheet
sheet = wb_style.active

for rows in sheet.iter_rows(min_row=2, max_row=6, min_col=1):
   for cell in rows:
     cell.font = Font(color="804000")

wb_style.save("test_Excel.xlsx")

Result:

Changing color of every other row (even rows)

In this example, we will change the text color of even rows i.e. every other row. Odd rows color remains the same as in the sample sheet while even rows color is changed to light green color.

This is done by getting the current row index and continue statement of Python.

See the code logic and result below:

The code:

# Change color of whole row
from openpyxl import load_workbook
from openpyxl.styles import Font

#Loading the Workbook
wb_style = load_workbook('test_Excel.xlsx')


#Accessing Active Sheet
sheet = wb_style.active

for rows in sheet.iter_rows(min_row=2, max_row=12, min_col=1):
   for cell in rows:
       if int(rows[0].row) % 2 == 0:
           print('Row number:', str(rows[0].row))
           cell.font = Font(color="00FF00")
           continue
wb_style.save("test_Excel.xlsx")

Resultant sheet:

Similarly, you may change the odd rows text color by modifying this line of code:

if int(rows[0].row) % 2 != 0:

Solution # 2 for even/odd row text coloring

Alternatively, you may use this code which is simpler and does not use a continue statement:

The code:

# Change color of whole row
from openpyxl import load_workbook
from openpyxl.styles import Font

#Loading the Workbook
wb_style = load_workbook('test_Excel.xlsx')

#Accessing Active Sheet
sheet = wb_style.active

for rows in sheet.iter_rows(min_row=2, max_row=12, min_col=1):

   for cell in rows:

       if not cell.row % 2:

           cell.font = Font(color="FF0000")

wb_style.save("test_Excel.xlsx")

and for odd rows coloring, modify this line of if statement:

if cell.row % 2:

Changing a column text color example

By using iter_cols function, you may change the partial or entire column color as well.

See the program below where we changed the color of the second column only.

Python Code:

# Change color of whole row
from openpyxl import load_workbook
from openpyxl.styles import Font

#Loading the Workbook
wb_style = load_workbook('test_Excel.xlsx')

#Accessing Active Sheet

sheet = wb_style.active

#Using iter_cols to change column color

for rows in sheet.iter_cols(min_col=2, max_col=2, min_row=2, max_row=None):

   for cell in rows:

     cell.font = Font(color="408080")

wb_style.save("test_Excel.xlsx")

Result: