16

I have a spreadsheet with approx 300 rows. Each row has about 15 columns, and each cell contains a numeric value. Each row corresponds to a product.

I want to get Excel, for each product, to highlight the cells with the highest numbers in red and the lowest numbers in yellow, with a gradient for numbers in between. This is what happens if I select a row (specifically, the 15 cells in the row which contain my data) and then click Conditional Formatting > Color Scales > Red-Yellow Color Scale.

However, I have 300 rows and it will take too long to select each row individually and set the Conditional Formatting. On the other hand, if I select the entire range and apply Conditional Formatting, Excel calculates the colours based on the entire range, when in fact I want them calculated on a row-by-row basis. For example, take this sample data:

1 2 3
4 5 6
7 8 9

The output I want, using Y for yellow, O for orange, R for red, is:

Y O R
Y O R
Y O R

However, if I select the entire range and apply Conditional Formatting, I get:

Y Y Y
O O O
R R R

Is there a way of accomplishing this without doing it one row at a time?

3
  • 1
    @wizlog: That's right. The highest value in a row should be red, the lowest value in that row should be yellow, and the other values should be somewhere in between (the closer to the highest value, the redder). Oct 25, 2011 at 16:45
  • I like this question... I'll see if I can find an answer... It'll probally be a few hours before I can help again, I hope other SuperUser members find you an answer in time through..
    – wizlog
    Oct 25, 2011 at 16:53
  • I like this question too. But I don't see any other way of approaching this other than using a macro. I hope someone proves me wrong though.
    – Ellesa
    Oct 25, 2011 at 18:12

5 Answers 5

18

Here's a macro that creates a conditional format for each row in your selection. It does this by copying the format of the first row to EACH row in the selection (one by one, not altogether). Replace B1:P1 with the reference to the first row in your data table.

Sub NewCF()
    Range("B1:P1").Copy
    For Each r In Selection.Rows
        r.PasteSpecial (xlPasteFormats)
    Next r
    Application.CutCopyMode = False
End Sub

To use, highlight the un-formatted rows in your dataset (in my case, B2:P300) and then run the macro. In the example below, note that the max numbers in the first two rows are 5 and 15, respectively; both cells are dark red.

I'm sure there's a faster solution than this, though.

enter image description here

4
  • 1
    Thanks, this worked beautifully. It's also a great example of the value of asking for help when unsure...I had been daunted by the idea of using a macro, since I assumed I would need to learn how to set the conditional formatting up from scratch. Getting the macro just to copy the existing formatting is much more cunning :) Oct 26, 2011 at 9:13
  • @MichaelPavey Glad I could help! And welcome to Superuser! :)
    – Ellesa
    Oct 26, 2011 at 17:45
  • 1
    + 1. If anybody's interested I took this concept further on my blog: yoursumbuddy.com/conditional-formatting-per-row-color-scales Feb 3, 2013 at 18:41
  • It is ridiculous that this sort of thing isn't built into Excel as a feature. Is it that uncommon to want to compare rows in this manner?
    – Alex
    Oct 16, 2013 at 6:59
8

The easiest way to accomplish this is with copy/paste incrementally. First, format 1 row the way you want it. Then copy and past the formatting only to ONLY a second row. Now copy BOTH rows 1 and 2 and paste the formatting to rows 3 and 4. Rinse and repeat, Copy 4, past 4, copy 8, paste 8, copy 16, paste 16. Once you've got a decent amount like 16, paste it a few times to get up to 64 or 128. Then you can copy these and paste their formatting, and you're exponentially covering more territory than before.

It's not elegant, and in my experience the resources required to conditionally format eat row start to get maxed out around 2500 rows... but its get the job done.

I just wish there were logic that didnt create a separate conditional format for every single row, hogging resources...

4
  • I think the OP said "However, I have 300 rows and it will take too long to select each row individually and set the Conditional Formatting." in the question
    – Prasanna
    Apr 6, 2016 at 7:15
  • @Poultron, thanks! To add to this: Using Ctrl/Cmd+Shift+Up/right you can readily select all previous rows and columns, so you don't need to count the previous rows. Then when you're ready to paste, simply position the cursor in the first cell below the already formatted rows and Special Paste Format only, it will extend to cover as many rows as were copied. Ctrl/Cmd+Shift+V will also remember the last special paste you want to use, so the whole process goes fairly quickly. Just used it on two spreadsheets with 600 rows each. Jun 27, 2016 at 20:51
  • I did this on a table with ~100 rows and it worked beautifully. Only took 7 iterations of Format Painter.
    – mskfisher
    Dec 21, 2018 at 15:32
  • If interested, you need log₂n of copy-pasting at max, where n is a number of rows
    – St.Shadow
    Apr 22, 2020 at 15:01
1

You can also apply the condition formatting to one row. Highlight the row again and double click on "Format Painter" to lock it. Use arrow down key to apply the condition down to other rows.

For my company usually blocks macro in excel so this works for me. hope it helps you too.

0

Just found this and other examples of how to do this via VBA and it got me thinking and I figured out a relatively quick and painless way of doing the same thing without having to know or copy any script.

Apply the desired Conditional Formatting to whatever row you want and then highlight the entire row. Next Right-Click anywhere along the border (mouse should be the Move icon) and drag down to the next row. In the context menu that pops up select "Copy Here as Formats Only". Now you should have your Conditional Format applied to both rows separately. Then continue to do this recursively except that now you can select and copy 2 or more rows at a time.

It's not a one shot deal like the above code but it is exponentially faster than doing one line at a time. Hope this helps.

0

I want to provide an updated code that works a bit more hands off. This is just an edit of @Ellesa's answer.

Sub RowConditionalFormatting()
    Selection.Copy
    For Each r In Selection.Resize(Selection.End(xlDown).Row - Selection.Row + 1, Selection.Columns.Count).Rows
        r.PasteSpecial (xlPasteFormats)
    Next r
    Application.CutCopyMode = False
End Sub

Just highlight the first row of the table with conditional formatting and run this macro. It will copy it all the way to the bottom of the table.

This just keeps from having to update the macro every time you need to run it.

You must log in to answer this question.

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