SOLVED

Can I format the rows of cells in a data table when the value in one column changes?

Copper Contributor

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 Table. For this example, I used the Sort & Filter function with the Editing tool. I then filtered each individual Payee and chose a highlight color. I could have also just sorted the rows by Payee and then highlighted again by selecting the rows with same Payee and highlighted that section.

 

Is there a function in Excel that will format these rows using a formula or other tool?

 

2022-01-13.png2022-01-13 (1).png

5 Replies
YES, conditional formatting. Select the region (i.e. A1:E35) then open conditional formatting choose to format based on a formula. The formula should be structured based on the upper left corner of the selected range as if you were going to fill down/right for all the other cells (i.e. use '$' as needed to 'lock' the column/row). So in your case a formula like
=($C1="Gas")
will highlight all cells in the same row as "Gas" in column C
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.
best response confirmed by AmbientConfusion (Copper Contributor)
Solution
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 @syedalix! Somebody smarter than I sure knows Excel better than Microsoft, it would seem. This is what I needed. I will pass the article on as further replies come in. Best Answer!
1 best response

Accepted Solutions
best response confirmed by AmbientConfusion (Copper Contributor)
Solution
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

View solution in original post