Formula, Conditional Formatting, or Macro for banded coloring rows of cells?

New Contributor



I am trying to set up some sort of automation to color rows to differentiate orders based on PO#. I have included an example below. We are looking to easily visualize when one PO# stops and another begins. As it stands, this is a manual process but I would love to automate it.


As you can see, each PO# is colored in to separate it from the previous PO#'s color by either being grey or white. I don't know how to set up a formula or anything to make excel color a row in this fashion. If anyone can help or offer a solution that would be incredibly helpful.




Thanks in advance! Looking forward to getting this automated!


2 Replies
best response confirmed by BromanGladiator (New Contributor)


See the attached version. I created a conditional formatting rule of type formula.

To inspect the rule, select A2 and click Conditional Formatting > Manage Rules... on the Home tab of the ribbon, then click Edit Rule...

@Hans Vogelaar 


Thanks so much! This works perfectly!