[Solved] Format Alternate Rows Based on Conditions

[era]

Hello,

If it is possible, I am looking for help in creating some rules that will highlight a row based on a supervisors name in column E and the employees name in column D, then if there are two employees who share the same supervisor to have the row alternate colors based on the employee's name (example table below). I also utilize down down headers so the supervisors can only select their name and therefor only see their employees, hopefully the formula would work when they do this. I have around 8 supervisors and 120 employees on this spreadsheet that I create and send out weekly. Thanks!!

For example:

Employee's Name (header)	Supervisor's Name (header)
Employee Name A	Supervisor Name A (row is light red)
Employee Name A	Supervisor Name A (row is light red)
Employee Name B	Supervisor Name A (row is red)
Employee Name C	Supervisor Name B (row is light blue)
Employee Name D	Supervisor Name A (row is light red)

 

[shamimarita]

Hello Era,

You can use conditional formatting with a custom formula in Excel to accomplish this. Here’s how you can set it up:

• Select the cell range, including both the Employee and Supervisor columns.
• Go to Home >> Conditional Formatting >> select New Rule.
• Choose "Use a formula to determine which cells to format".
  • Enter the following formula to alternate colors based on the employee and supervisor combination:
  • =ISODD(COUNTIF($D$22, $D2) + COUNTIF($E$2:E2, $E2))
  • This formula checks each unique Employee-Supervisor combination to alternate colors.
• To Set Formatting:
  • Choose a light red or red fill color for the first rule.
• Click OK.

Repeat steps 2-4 to set up a second rule with the same formula but with a different color (e.g., light blue).

Test with Dropdown:

When supervisors filter by their names, the rules should adjust dynamically as they only display their employees.
This setup will apply alternating colors to rows with matching Employee-Supervisor combinations, making your report easy to read. Let me know if you need further help!

 

[era]

Hello shamimarita,

Thank you for responding so quickly!

I copied over your formula and It is unfortunately not working for me. I have included a screenshot of what the formula is doing. I did have to blur out some information but kept the names and non-confidential data visible.



Thank you again for your help!

 

[shamimarita]

Hello Era,

Thank you for your response and the screenshot—it’s very helpful. Let’s adjust the approach:

New Formula: Try this formula for alternating colors based on unique combinations of Employee and Supervisor:

=MOD(SUMPRODUCT(($E$2:$E2=$E2)*($D$2:$D2=$D2)), 2) = 0

This formula alternates colors between rows with the same Employee-Supervisor pairs.

Apply Different Colors:
Set two conditional formatting rules: one with the formula above and a color (e.g., light red) and another with the opposite condition and a different color.

 

[era]

Hello again Shamimarita,

This is almost there, it is applying the opposite effect. It is alternating the colors when the drivers name (column D) stays the same and keeping the color same when the drivers name change. I don't have the ability to share a screen shot at the moment so I do apologize.

For reference I tried to produce the opposite condition.
=MOD(SUMPRODUCT(($E$2:$E2=$E2)*($D$2:$D2=$D2)), 2) = 0
=MOD(SUMPRODUCT(($E$2:$E2=$E2)*($D$2:$D2=$D2)), 2) <> 0

and

=MOD(SUMPRODUCT(($E$2:$E2=$E2)*($D$2:$D2=$D2)), 2) = 0
=MOD(SUMPRODUCT(($E$2:$E2=$E2)*($D$2:$D2=$D2)), 2) = 1

Thank you for your help on this problem!


Edit:

I was able to get the formatting to work as I need to. I did have to create a helper column though. This is what I did.
In column Z, helper column, I used,

=IF(AND(E2=E1, D2=D1), Z1, ROWS($Z$2:Z2)) and dragged it down my spreadsheet.

Then for conditional formatting I used,

=MOD($Z2, 2) = 0
=MOD($Z2, 2) = 1

Thank you again for all your help on this problem. I hope you have a fantastic day.

 

[shamimarita]

Hello Era,

Thank you for following up, and I'm glad you found a solution that works for you!

A helper column is a great approach when working with complex conditional formatting, especially when you must alternate colors based on multiple conditions. Your method with the helper column (Column Z) makes tracking the driver and route details much easier, allowing for consistent formatting without relying on longer formulas in conditional formatting.

Thanks for sharing your solution, and I'm happy to have been able to help along the way. Have a wonderful day too!