I’m trying to highlight (as in change the background colour of the cell) the minimum non-zero value in a row of numbers. I can set up conditional formatting to do this, but whenever I add a new row of numbers, the formatting extends to include the new row (e.g. highlight minimum of range B2:B6, insert a new row below, range is now B2:C6). But the new row needs to be compared against itself and not any other rows (row B compared only to row B, row C compared only to row C, etc.). How can I do this without having to manually adjust the formatting for every single row of the worksheet whenever I add new rows?
And clone-formatting doesn’t work for this either. It, also, changes the range of the formatting instead of applying the formatting to the new range (e.g. range B2:B6 gets cloned onto D2:D6, range is now B2:B6;D2:D6; it should be range B2:B6 and a separate rule for range D2:D6).
This is especially aggravating because I never had these problems in Office (it applied formatting exactly the way I expected it to) and I really don’t want to shell out $100+ for an Office licence to fix this one issue.