Conditional Formatting for discrete rows (two part question)

Occasional Visitor

First, I've searched and seen a number of answers to similar questions and tried/failed each one. Here's the scenario:


I have a table of citizen science data about 100 columns (historic years) by 200 rows (counts by species). I am simply trying to find a way to conditionally format each row (species) to highlight the maximum number, without having to repeat the manual process for each of the 200 rows. I have a cell to the right of the table that shows =MAX(range), but the highlights are a visual that helps me in the table itself.


Copy down > Copy format only simply expands the rule to two rows, rather than duplicating the rule for the next row, thus fails to meet the need. If I try to edit the rule to remove the absolute cell reference, it reverts to the original rule when I save. In the Manage Rules dialog box, there isn't a way to create a copy of a rule and edit the Range, which would be a simple and effective solution.


Second question: I want to create a conditional formatting rule, similar to my first question, to highlight the lowest non-zero value in each row of the same table.


I must be missing something (besides patience).

2 Replies


Maybe as shown in the attached file.