Excel条件格式化一行中的3色应用于多行 [英] Excel Conditional Formatting 3-color over one row applied to many rows

问题描述

我有一些excel电子表格，几百到几千行。每行代表以一秒间隔进行的一组测量。

我想为每一行使用3色条件格式。我可以通过将适用于设置为3颜色的单行：

 'Table1'！$ B $ 2：$ M $ 2 
  

但是，如果我尝试复制格式，然后将其应用于多个行（例如B3：M400），它会将整个块（从B3到M400的所有单元）视为单个条件格式，因此每个单元根据所有其他单元进行着色。我正在寻找的是一种将三种颜色条件格式化应用于多行的每一行的方法。

例如，在此处链接的图像中： a href =http://electriceptor.files.wordpress.com/2012/04/screen-shot-2012-04-30-at-11-53-36-pm.png =noreferrer> http： //electriceptor.files.wordpress.com/2012/04/screen-shot-2012-04-30-at-11-53-36-pm.png （抱歉，我太新帖不能张贴图片），每行都有一个单独的3色格式，我手动应用于每一行。

但是，如果我复制一行并将格式粘贴到同一个7行的块中，它看起来像这样的图像： http：//electriceptor.files.wordpress。 com / 2012/04 / screen-shot-2012-04-30-at-11-56-42-pm.png ：

请注意

有没有办法单独应用每个行的条件格式化，而不是手动执行每一行？

解决方案

由于3色条件格式不接受相关引用（无论您尝试用code> INDIRECT / code>， ADDRESS 等），最好的方法是使用这里的方法（例如是2色格式化）：https://superuse r.com/questions/350378/excel-2007-conditional-formatting-so-that-each-row-shows-low-values-yellow-hig

这与使用每个单独行上的格式画家一样（使用多行的格式画家可以让您回到正方形），

  Sub NewCF（）
 Range（B1：M1）。复制
对于每个r在Selection.Rows 
 r.PasteSpecial（xlPasteFormats ）
下一个r 
 Application.CutCopyMode = False 
 End Sub 
  

此外，条件格式化规则的数量没有限制（至少在Excel 2010中），但是您拥有的越多，对性能的负面影响的潜力就越大。你只需要尝试看看。在最坏的情况下，我将使用3-10（或者你可以支持多少）根据公式创建一个渐变的规则，但这可能与cpu密集。

I've got some excel spreadsheets with a couple hundred to couple thousand rows. Each row represents a set of measurements taken at one second intervals.

I want to use the 3 color conditional formatting for each row. I can setup a single row for the 3 color by setting the "Applies to" to something like:

 'Table1'!$B$2:$M$2

However, if i try to copy the format and then apply it to multiple rows, (say B3:M400), it will treat the entire block (all cells from B3 through M400) as a single conditional format, so each cell is colored according to all of the other cells. What i'm looking for is a way to apply the 3 color conditional formatting to each row individually over many many rows.

For example, in the image linked here: http://electriceptor.files.wordpress.com/2012/04/screen-shot-2012-04-30-at-11-53-36-pm.png (sorry, i'm too new to post images), each of the rows has an individual 3 color format that i manually applied to each row.

However, if i copy a single row and paste the format into that same block of 7 rows, it looks like this image: http://electriceptor.files.wordpress.com/2012/04/screen-shot-2012-04-30-at-11-56-42-pm.png:

Note how the formatting is applied to the entire block.

Is there a way to apply the conditional formatting for each row individually without doing each row manually?

解决方案

Since 3-color conditional formatting doesn't accept relative references (no matter how much you try to 'trick' Excel with INDIRECT, ADDRESS, etc.), your best bet is to use the method here (example was for 2-color formatting): https://superuser.com/questions/350378/excel-2007-conditional-formatting-so-that-each-row-shows-low-values-yellow-hig

This is the same as using the format painter on each individual row (using the format painter on multiple rows puts you back to square one where it looks at all the rows).

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

Also, there's no limit to the number of conditional formatting rules (at least in Excel 2010), but the more you have, the more potential there is for a negative impact on performance. You'll just have to try it and see. In the worst-case scenario, I would make 3-10 (or however many you can stand to make) individual rules based on a formula to create a "gradient", but this may be just as cpu-intensive.

这篇关于Excel条件格式化一行中的3色应用于多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！