如何仅对可见单元格应用条件格式? [英] How to apply conditional formatting for only visible cells?

问题描述

我正在使用Excel-2010，我已对Excel范围A1:F100应用了3scale条件格式，在Dropdown宏上将执行隐藏/取消隐藏操作，并且隐藏/取消隐藏操作不是连续的，例如:-行1,10 ，30,54,67,88可能仅可见.

I am using Excel-2010, I have applied 3scale conditional formatting for Excel range A1:F100, on a Dropdown macro hide/unhide will be actioned, and hide/unhide is not sequential, for eg:- Rows 1,10,30,54,67,88 may only visible.

因此，仅对于这些可见行，相同的条件格式应该起作用.

So for only these visible rows, the same conditional format should Work.

我尝试浏览找到它，但是我找不到所需的东西.

I have tried browsing to find it, but i couldn't get the required.

非常感谢帮助.

谢谢.

推荐答案

如上所述，应该是这样的:

As commented, it should be something like this:

Sub ject()
    Dim rng As Range
    With Sheet1 '~~> change to your actual sheet
        .Range("A1:F100").FormatConditions.Delete
        Set rng = .Range("A1:F100").SpecialCells(xlCellTypeVisible)
        .Range("A1").FormatConditions.AddColorScale 3
        With .Range("A1").FormatConditions(1)
            With .ColorScaleCriteria(1)
                .Type = xlConditionValueLowestValue
                .FormatColor.Color = RGB(255, 0, 0)
            End With
            With .ColorScaleCriteria(2)
                .Type = xlConditionValuePercentile
                .FormatColor.Color = RGB(255, 255, 0)
            End With
            With .ColorScaleCriteria(3)
                .Type = xlConditionValueHighestValue
                .FormatColor.Color = RGB(0, 255, 0)
            End With
            .ModifyAppliesToRange rng
        End With
    End With
End Sub

每次运行或调用此例程时，它将格式重新应用于可见范围.
它可以合并到现有代码中或单独运行. HTH.

Everytime this routine is run or called, it re-applies formatting to visible ranges.
It can be incorporated to an existing code or run separately. HTH.

这篇关于如何仅对可见单元格应用条件格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！