Say I have the following simple data in Excel:
A B C D E F G
2 1 3 2 5 4
2 4 2 1 3 2
5 2 1 3 2 4
I want to set up conditional formatting to highlight any rows from a selection where the smallest number is 1. In this case I select cells A1:C3, and apply formatting using the formula =SMALL($A1:$C1,1)=1
. A1:C1 and A3:C3 are highlighted as expected. Note that the conditional formatting does not work as intended without the absolute references.
Is there a way to create a macro that will let me select any group of cells (say E1 to G3) and quickly apply the same conditional formatting to the selection? Say I select the range E1:G3, cells E2:G2 should highlight.
I get that the absolute references here are the issue. I've tried setting up a macro but when I apply it to a new range, the formatting retains the absolute references to columns A:C. Is there any way around this though?