Forum Discussion
conditional formatting help
I have run into a situation but I have to format rows instead of columns. For example, I need to use the MAX formula to highlight the top number in a row of cells, (D4:T4) and I need each subsequent row to have the same conditional formatting. I have two problems. When I enter in the conditional formatting formula in Conditional formatting>new rules>use formula, the formula I enter is =MAX($D4:$T4). However, once I apply, the entire row is highlighted green, and I am just looking for the highest value. Additionally, how do I quickly copy the same formatting for the next hundred rows, ensuring that the conditional formatting is just used in each row?
3 Replies
Hi Noel,
While being on Cell D4, please enter =D4=MAX($D4:$T4) in the 'Edit Formatting Rule --> Use a formula to determine which cells to format' window and set the required formatting. After this, go to 'Conditional Formatting Rules Manager, and put =$D$4:$T$104 into the 'Applies To' window. Please see the example attached.
Yury
Thank you Yury, this helps a lot!
Question about the formula, what is the significance of the first cell reference =D4=MAX($D4:$T4) is that just part of the overall formula. If that cell is not references, the entire table is highlighted with green color that I use in the conditional formatting
Hi Noel,
D4 points to current cell to be evaluated against the MAX rule. The fact that the reference is not absolute allows it to move to the right or down in relation to the start cells of the 'Applied to' range (which is $D$4). In every cell of the row it would evaluate if that cell represents a maximum of the given row range. For example, in H4 (please see my examplef file) it would evaluate if 100 is a max of the range $D4:$T4, and in O5 it would evaluate if 99 is a maximum of the range $D5:$T5. Note, that in the latter example the row range would shift down, because, once again, in the formula MAX($D4:$T4) the row reference is relative (to the first cell of the 'Apply to' range, being $D$4).
Hope this helps
Yury
