返回条件格式单元格内部颜色的函数 [英] Function to return interior colour of conditional formatted cell

问题描述

我想要一个返回条件格式的单元格的颜色索引值的函数。

I want a function that returns a value for the colour index of a conditionally formatted cell.

它用于检查一行中如果它们使用条件格式突出显示，则需要执行操作-检查单元格中是否有颜色比创建覆盖所有单元格条件的组合条件公式更简单。还是我想..

It is being used to check a number of cells in a row that if they highlighted using conditional formatting then an action will be required - it is simpler to check if there is a colour in the cell rather than create a combined conditional formula covering the conditions of all the cells. Or so I thought..

尽管该代码在msg框中起作用，但是下面的代码返回了#VALUE错误。.

The code below is returning a #VALUE error although the code works in a msg box..

Function fillcolour(rng as Range) as variant

    fillcolour=rng.Displayformat.Interior.ColorIndex

End Function

期望看到返回的颜色索引的值但得到#VALUE

Expect to see a value for the colour index returned but getting #VALUE

推荐答案

使用更改事件：

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    Call fillcolour(Cells(1, 1))
    Application.EnableEvents = True

End Sub



Function fillcolour(ring As Range) As Variant

    fillcolour = ring.DisplayFormat.Interior.Color
    Cells(1, 2) = fillcolour

End Function

每次更改单元格的内容时，都会向您显示单元格B1中的背景颜色（即使它来自条件格式）。

Each time you change the contents of a cell it will show you the background color in cell B1 (even if it comes from a conditional format).

但是，此事件返回一个目标，其中包含导致该事件的行和列（目标。行，目标列，等等。您可以使用此目标来检测您直接在单元格上所做的更改。

However, this event returns a target with the row and column that caused the event (Target.Row, Target.Column, among many other things). You can use this target to detect the change you have made directly on the cell.

在新计算期间单元格发生更改时，不会发生此事件。使用Calculate事件来检测您的范围/单元格中的变化。

This event does not occur when the cells change during a new calculation. Use the Calculate event to detect a change in your range / cell.

这篇关于返回条件格式单元格内部颜色的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！