1

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?

1 Answer 1

0

You can still use conditional formatting, but you need to apply it carefully.

Select A1 to A3 and apply

=SMALL(A1:C1,1)=1

Select B1 to B3 and apply exactly the same rule with relative references.

=SMALL(A1:C1,1)=1

Select C1 to C3 and apply the same rule again.

=SMALL(A1:C1,1)=1

Now you can copy the formats from A1 to C3 in one go and paste them onto cell E1. The result is in the screenshot below.

Keep in mind that references are relative to the CURRENT cell. So you just need to create three rules instead of one to cover the three different scenarios in the range with three columns.

enter image description here

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .