Q:

How can we use conditional_format() function from XLSXWriter to highlight a single cell based on multiple conditions?

I am trying to write a code which takes excel sheet as input and write output to new excel sheet. The input is same file without highlight.
The conditions to be considered
1. Highlight the cells in column 'marks' with yellow color if it is greater than 80
2. Following 3 are assumptions for case 2:
2.1. For column Div_No 'B' and column name 'v, if value of marks == value of CGPA then highlight CGPA with yellow
2.2. For column Div_No 'B' and column name 'v' if value of marks > value of CGPA then highlight CGPA with green
2.3. For column Div_No 'B' and column name 'v' if value of marks < value of CGPA then highlight CGPA with redBelow is the image showing the required output in excel

A:

I do not think so that this is possible with Excel's built in conditional formatting. However it is possible using pandas and a for loop. I don't know if this helps you as the format on column 'CGPA' will not follow any changes you make after the creation of the file, but at least you can format it when you are creating it. This is how i would do it:
import pandas as pd

# Create your dataframe
df = pd.DataFrame({'Div_No': ['A','A','A','A','B','B','B','C','C'],
                   'Roll_no': [x for x in range(1,10)],
                   'name': ['a','b','g','s','v','v','v','v','k'],
                   'marks': [54,60,54,67,43,88,54,91,66],
                   'CGPA': [54,61,53,66,46,88,51,90,65]})

# Capture the index number where it fulfils your criteria
# 1st column must equal to B, 3rd column must equal to v
rows_that_fulfil_criteria = df.index[(df['Div_No']=='B') & (df['name']=='v')]

# Kickstart the xlsxwriter
writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Define the formats for the conditional formating
cell_format_red = workbook.add_format({'bg_color': 'red'})
cell_format_yellow = workbook.add_format({'bg_color': 'yellow'})
cell_format_green = workbook.add_format({'bg_color': 'green'})

# Conditional formatting for column marks is easy, just use the built in
worksheet.conditional_format('D2:D1048576', {'type': 'cell', 'criteria': '>',
                              'value': 80, 'format': cell_format_yellow})

# Conditional formatting for column CGPA
# If it matches the index number that we captured earlier then
# compare the values between columns marks and CGPA
for row in rows_that_fulfil_criteria:
    if df.iloc[row,3] == df.iloc[row,4]:
        worksheet.write(row+1, 4, df.iloc[row,4], cell_format_yellow)
    elif df.iloc[row,3] > df.iloc[row,4]:
        worksheet.write(row+1, 4, df.iloc[row,4], cell_format_green)
    elif df.iloc[row,3] < df.iloc[row,4]:
        worksheet.write(row+1, 4, df.iloc[row,4], cell_format_red)

writer.save()

