Excel 2007: Conditional formatting so that each row shows low values yellow, high values red

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.

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...

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.

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.