Forum Discussion
Solved
Can I format the rows of cells in a data table when the value in one column changes?
I downloaded banking transactions, which were sorted by the 'Send on' date. I want to format rows with the same 'Payee' with the same color efficiently using Excel Conditional Formatting or Format as...
- Reply is to Microsoft Excel department directly. Why are such common needed features like simply shading rows based on changing value in a single column not automatically provided as a select column & apply? Why do people have to go through the long winded route explained via this link:
https://www.extendoffice.com/documents/excel/2661-excel-alternate-row-color-based-on-group.html
Thanx mtarler. That works but requires lots of input on the formula for each category. It does keep me from sorting, filtering and then formatting. I am hoping to find a way to do all this in one formula or function with minimal input from me each time. I used to be more familiar with macros but with little use in the past 30 years, have forgotten most of it, unfortunately. Could I write one macro to perform this operation?
short answer is yes.
the issue comes up on how to implement it. for example how do you assign colors? do you randomly pick? do you use the built-in palette (easy but then limited in number of colors)? You could also use a table but then need to list all the categories and what color you want to use.
In the end, unless you creating this template for many different people and different potential categories, it sure seems easier to just duplicate the conditional formatting rule and tweak the category name and color.
the issue comes up on how to implement it. for example how do you assign colors? do you randomly pick? do you use the built-in palette (easy but then limited in number of colors)? You could also use a table but then need to list all the categories and what color you want to use.
In the end, unless you creating this template for many different people and different potential categories, it sure seems easier to just duplicate the conditional formatting rule and tweak the category name and color.