设置每一行的前3个值和后3个值 [英] Format Top 3 and Bottom 3 Values for each row

问题描述

我认为我可能需要VBA宏才能解决此问题。我有一个大约10,000行的数据集，其中包含15列值，我想对每一行通过有条件地格式化前三个值和后三个值来突出显示。

I think I may need a VBA macro to solve this. I have a data set of approx 10,000 rows, with 15 columns of values, and what I want to do is, for each row, highlight by conditional formatting the top three and bottom three values.

我已经使用xl2010中的条件格式化工具为第1行设置了规则，但是当我在剩余的9,999行中复制粘贴特殊格式时，结果是仅格式化前三和后三

I have set the rule up by using the conditional formatting tool within xl2010 for row 1, but when I copy-paste special-formats over the remaining 9,999 rows, the result is to format only the top three and bottom three values that are contained in the 9,999 rows.

我希望看到每行的前3位和后3位阴影，而不是整个数据集，最好不进行复制粘贴特殊的9,999次！

I want to see this shading the top 3 and bottom 3 for each row, and not the whole data set, ideally without copying paste special 9,999 times!

推荐答案

宏记录功能对于这类问题特别有用，特别是对于初学者（我不是

The macro record feature is good for these types of issues, especially for beginners (I'm not all that savvy in VBA myself).

这将突出显示红色的前三个值，蓝色的后三个值。
注意我的i从1到1000，根据需要进行更改（与列部分相同）。

This will highlight the top 3 values red, and the bottom 3 blue. Note I have i from 1 to 1000, change as needed (same for column section).

编辑：针对您的范围进行了更改，我没有阅读

Changed for your ranges, I didn't read them the first time.

Sub Conditions()
Dim myrange As Range


For i = 1 To 10000

Set myrange = Range("A" & i & ":" & "O" & i)
myrange.FormatConditions.AddTop10
myrange.FormatConditions(myrange.FormatConditions.Count).SetFirstPriority

With myrange.FormatConditions(1)
    .TopBottom = xlTop10Top
    .Rank = 3
    .Percent = False
End With
With myrange.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
End With

myrange.FormatConditions(1).StopIfTrue = False
myrange.FormatConditions.AddTop10
myrange.FormatConditions(myrange.FormatConditions.Count).SetFirstPriority

With myrange.FormatConditions(1)
    .TopBottom = xlTop10Bottom
    .Rank = 3
    .Percent = False
End With
With myrange.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 15773696
    .TintAndShade = 0
End With

myrange.FormatConditions(1).StopIfTrue = False

Next

End Sub

这篇关于设置每一行的前3个值和后3个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！