I am currently managing a live spreadsheet that updates regularly. The spreadsheet is a compilation of lists that change in length over time, that are imported using the "importrange' function. Each list has one total at the very bottom row. And then a new list begins.
Currently, I manually change the fill colour for each of the totals rows a specific colour. But when the lists change in length, values will be pushed down rows, but the colouring will remain at the original cells.
I have set up conditional formatting (I applied 'if value is greater than 0' to the B column) to colour the (single) total value in the totals row. But I want the entire row to be coloured. Would there be a way to write a macro or conditional formatting rules to colour the entire row?
I have an example of what I want the spreadsheet to look like below. The letter 'a' means that there will be words in the cell, and the number '1' means that there will be numbers in the cell. There will be one 'total' value, and I want the entire row to be coloured if there is a total value.
Image of example spreadsheet layout
ie. cells B6 and B15 are totals of the number of entries above (using the COUNTA function), that have been coloured based on conditional formatting. I want the macro/conditional formatting to then colour the entire rows 6 and 15 automatically so it looks like the image.
I am interested to learn about how the specific conditional rules if anyone is willing to explain/link something. Otherwise, just the rule is fine.
Use this conditional formatting custom formula rule for the range A2:H:
=search("count", formulatext($B2))
See GimelG's explanation and Lance's explanation of how absolute and relative addresses work in a conditional formatting custom formula rule.