Conditional formatting independently for multiple rows

Question

Hello,

I have already googled for the answer but cannot find exactly what I need.

I have an excel spreadsheet with 4 columns with different numbers in each cell.

There are about 450 rows to the table.

For each row, I would like to apply the 2-colour scale conditional format to show which values are highest and which are lowest (and which come in between).

I then want to apply this format to all other rows.

The issue I have had is I can only get it to format a colour scale for the spreadsheet as a whole, rather than independently colour scale for each row.

Does anyone have any suggestions how to independently colour scale each row?

Thanks in advance.

Answer 1

Hi,

You want the largest1, largest2... per Row. So, from your last picture formula must be:

=C13=LARGE($C13:$F13,4)

Same goes for the other rules

=C13=LARGE($C13:$F13,3)

=C13=LARGE($C13:$F13,2)

=C13=LARGE($C13:$F13,1)

Answer 2

Set the conditional formatting rule for the first row that you want to apply it to.

Then select the entire range.

Run the following macro:

Sub CopyCF()

    Dim rng As Range

    Dim r As Long

    Set rng = Selection

    rng.Rows(1).Copy

    For r = 2 To rng.Rows.Count

        rng.Rows(r).PasteSpecial Paste:=xlPasteFormats

    Next r

End Sub

Warning: the macro will create 450 conditional formatting rules!

Answer 3

Home tab > Conditional Formatting > Manage Rules > Select the box above to show rules for this worksheet > Now select your rule > In Applies to box, change the range appropriately say A2:D451

Answer 4

@Vijay_Verma: that won't apply the colour scale for each row independently, as requested by the OP.

Answer 5

Hi

If it was me I would create 4 Cond. Format. rules with formulas that mimic the 2-colour scale:

All rules applies to =$A$2:$D$21 in the above example, rules formula:

1. =A2=LARGE($A2:$D2,4)
2. =A2=LARGE($A2:$D2,3)
3. =A2=LARGE($A2:$D2,2)
4. =A2=LARGE($A2:$D2,1)

• with 450 rows  would check Stop If True next to each rule for obvious perf. reasons: