打印单元格颜色（ColorIndex或RGB）的Excel公式

Question

打印单元格颜色（ColorIndex或RGB）的Excel公式

3

在Excel中，是否有一种公式可以检索单元格的ColorIndex（或RGB）？

我找到了以下函数：

CELL(info_type, the_cell)

这里有文档记录，但没有关于单元格颜色的参考信息。

虽然有color信息，但对我来说毫无用处。实际上，它的描述如下：

"color" 如果单元格为负值而设置了颜色，则返回值为 1；否则返回 0 (零)。

有什么想法吗？

此外，我发现可以使用VBA属性Cell.Interior.Color来完成此操作，但实际上我并没有使用宏，而是简单的Excel公式。也许有一种方法可以用公式模拟VBA函数吗？

- Teejay

1

我担心许多Excel对象的属性只能从VBA中访问。但是你想要的信息可以通过三行代码获得，并且你可以在Excel表格中像使用内置公式一样使用这样的自定义公式。如果需要，我可以为您发布一个VBA解决方案。=CELL("color"...)与条件格式有关。 - MikeD

1

老实说，我不知道什么是3-liner，Google似乎也没有帮助我。如果您能解释一下，我会很感激的 :) - Teejay

抱歉我的术语... 3行代码指的是3行VBA代码。 - MikeD

哦，好的！那么解决方案就是创建一个VBA宏，将其保存在工作簿中，然后构建一个使用该宏的公式？ - Teejay

是的，确实如此。我刚刚为您发布了一个。 - MikeD

3个回答

0

以下函数将显示所选单元格的RGB值。

Function CellColorValue(CellLocation As Range)
    Dim sColor As String

    Application.Volatile
    'Retrieve hex value into string sColor    
    sColor = Right("000000" & Hex(CellLocation.Interior.Color), 6)
    'Return the string Version e.g. 255,255,255 RGB color value found in 
    'Excel cell. Use in built worksheet function to convert Hex to Decimal
    'Use string function to separate Hex string into three parts
    CellColorValue = Application.WorksheetFunction.Hex2Dec(Right(sColor, 2)) & "," & application.WorksheetFunction.Hex2Dec(Mid(sColor, 3, 2)) & "," & Application.WorksheetFunction.Hex2Dec(Left(sColor, 2))
End Function

- MJH

0

请尝试以下代码：

更改内容：请查看代码中的注释

模块

Public Function Performance_Message(NonPreferredAvg As Single _
                                  , NonPreferredAvgname As String _
                                  , PreferredAvg As Single _
                                  , PreferredAvgname As String _
                                  , Optional Outputtype As String _
                                   ) As Variant

    Dim performancemessage As String
    Dim averagedifference As Single
    Dim stravgdif As String
    Dim cellcolor As String

    averagedifference = Abs(NonPreferredAvg - PreferredAvg)
    stravgdif = FormatPercent(averagedifference, 2)

    Select Case PreferredAvg
        Case Is < NonPreferredAvg
            performancemessage = PreferredAvgname & " Is " & stravgdif & " Less Than " & NonPreferredAvgname
            cellcolor = 4 '"green" 'Changes made

        Case Is = NonPreferredAvg
            performancemessage = PreferredAvgname & " Equals " & NonPreferredAvgname
            cellcolor = 6 '"yellow" ''Changes made

        Case Is > NonPreferredAvg
            performancemessage = PreferredAvgname & " Is " & stravgdif & " Greater Than " & NonPreferredAvgname
            cellcolor = 5 '"blue" 'Changes made
        Case Else
            performancemessage = "Something Bad Happened"
    End Select
    If Outputtype = "color" Then
        Performance_Message = cellcolor
    Else
        Performance_Message = performancemessage
    End If
End Function

工作表

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myColor As Double
  myColor = Target.Value ''Changes made
  Call SetPerformancecolor(Target, myColor)
End Sub

Private Sub SetPerformancecolor(Target As Range, myColor As Double)
  Target.Interior.ColorIndex = myColor ''Changes made
End Sub

- Karthick Gunasekaran

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MikeD · Accepted Answer

以下是您的一些小函数。从您的工作表中，按下Alt-F11打开VBA编辑器，插入一个新模块，粘贴下面的代码，然后回到您的工作表并使用它们的名称，如=FillColor(A1) 前两个是承诺的“三行代码”，可以给出字体和背景颜色的十进制值——但并不是很有用

第二个函数将十进制数转换为RGB，并返回格式为N、N、N的字符串

第三组是数组公式——选择一行中的3个单元格，输入公式，按Ctrl+Shift+Enter在3个相邻的单元格中获取数字RGB值

Function FillColor(Target As Range) As Variant
    FillColor = Target.Interior.Color
End Function

Function FontColor(Target As Range) As Variant
    FontColor = Target.Font.Color
End Function

Function FillColorRGB(Target As Range) As Variant
Dim N As Double

    N = Target.Interior.Color
    FillColorRGB = Str(N Mod 256) & ", " & Str(Int(N / 256) Mod 256) & ", " & Str(Int(N / 256 / 256) Mod 256)
End Function

Function FontColorRGB(Target As Range) As Variant
Dim N As Double

    N = Target.Font.Color
    FontColorRGB = Str(N Mod 256) & ", " & Str(Int(N / 256) Mod 256) & ", " & Str(Int(N / 256 / 256) Mod 256)
End Function

Function FillColorRGBArray(Target As Range) As Variant
Dim N As Double, A(3) As Integer

    N = Target.Interior.Color
    A(0) = N Mod 256
    A(1) = Int(N / 256) Mod 256
    A(2) = Int(N / 256 / 256) Mod 256
    FillColorRGBArray = A
End Function

Function FontColorRGBArray(Target As Range) As Variant
Dim N As Double, A(3) As Integer

    N = Target.Font.Color
    A(0) = N Mod 256
    A(1) = Int(N / 256) Mod 256
    A(2) = Int(N / 256 / 256) Mod 256
    FontColorRGBArray = A
End Function

注意：更改单元格的颜色并不会启动上述函数/公式的重新计算，因为通常情况下重新着色单元格并不能驱动重新计算。您需要使用Ctrl+Alt+Shift+F9手动开始完全重新计算。