CODEWITHSUNDEEP
excelexcel-formulaexcel-2013conditional-formatting
larryltj
larryltj

Reputation: 15

Conditional formatting for multiple rows (highlight lowest value in each)

I have a table in a sheet in Excel with over 2000 rows and about 500 columns of data (numbers). Values in a particular column header are being compared to each row underneath that header, and I am using a VBA function to calculate these values (in other words, I am comparing the value in B1 to the values in A2 through A2146, the value in C1 to the same range, etc). I want to highlight the lowest number in each row by filling the cell. I have researched this issue and tried to solve the problem by implementing this solution, but by using MIN instead of MAX:

=A1=MIN($A1:$TX1)

Columns A through TX contain data.

However, when I use this function as a formatting rule, Excel highlights the wrong values and sometimes multiple values in a row. For example, I have a row with a cell containing the value 10.2, which is the only cell highlighted in this row, even though it is neither the MIN nor MAX of the specified row. Additionally, I have another row which contains values 10.6, 8.4, 5.2, and 20.8. Each one of these values is highlighted based on the formatting rule above, though 5.2 is the obvious MIN; the other three cells with the aforementioned values, then, should not be formatted.

How can I remedy this? I have tried adjusting relative/absolute cell references to determine if this could be the problem, but I am quite sure that the way I'm referencing the cells is correct.

Upvotes: 0

Views: 2751

Answers (1)

pnuts
pnuts

Reputation: 59442

Courtesy @barry houdini (copied from a Comment):

Select the whole range of numbers B2:TX2146 (you can do that by typing that range in the box above A1 then press RETURN). Now set CF with the formula that applies to top left cell, i.e. =B2=MIN($B2:$TX2)

Upvotes: 0

Related Questions