Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
@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). –  Michael Pavey Oct 25 '11 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 '11 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 '11 at 18:12

1 Answer 1

up vote 4 down vote accepted

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

share|improve this answer
    
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 :) –  Michael Pavey Oct 26 '11 at 9:13
    
@MichaelPavey Glad I could help! And welcome to Superuser! :) –  Ellesa Oct 26 '11 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 –  Doug Glancy Feb 3 '13 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 '13 at 6:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.