I want to make it so if all of the cells in a row has data that matches, it will automatically change the row's fill color to green.

Example of the desired visual effect, including my conditional formatting work-around to achieve the look

As you can see in the image, the rows that have empty cells are not colored green.

Some other functionality I would also appreciate:

• Make a row recolor to red if it contains cells that have mismatching data. (such as someone putting their name in someone else's row)
• If data is added to a previously empty row, expand the Protected Range by 1 row after 30 minutes.
• If a row within a Protected Range becomes empty, the Protected Range automatically sorts itself from A-Z by Column A and reduces its size by 1 row.

Context:

I've recently made a spreadsheet on Google Sheets for my guild in an MMO to track what professions and recipes everyone has, people with the link can perform edits on the sheet, but I do have protected ranges that cover older entries which have their editor access restricted to a small team so random people don't just delete all the data. None of the other officers that regularly maintain the spreadsheets we use have much experience with spreadsheets, which are pretty basic with little automation. It takes three to four of them to do my job when I'm on vacation despite teaching each of them how to maintain the spreadsheets. More automation would be nice for when I go on vacation and eventually retire from that MMO.

This is my first post on this site, I don't know computer programming, not that well-versed with spreadsheets, and trying to google this didn't help since everything I found talked about conditional formatting based on the columns, not the rows.

For now my temporary work-around is to manually add conditional formatting to a range each time I see a row has filled up to change the fill color to green if the cell contains exactly that person's name. Based on prior experience with my guild's other spreadsheets, I know the other officers would not do this themselves.

I'm not sure where to look for help with Google Sheets, but I have a family member that is a developer and I vaguely remember him mentioning this site which is why I'm giving it a shot.

CodePudding user response：

try:

=SUMPRODUCT($A2:$K2<>"")=11

on range A2:K

update:

=(SUMPRODUCT($A2:$R2=$A2)=18)*(A2<>"")

on range A2:R