打印单元格颜色的Excel公式（ColorIndex或RGB） [英] Excel formula that prints cell color (ColorIndex or RGB)

问题描述

我在Excel中找到一个公式来检索单元格的ColorIndex（或RGB）？我发现以下函数：

  CELL（info_type，the_cell）
  

记录了此处，但是它没有任何细胞颜色的参考信息。

这是一个颜色信息，但对我来说没用。
其实它的描述如下：

color如果单元格以负值格式化为颜色，则值为1;否则返回0（零）。

任何想法？

我发现这样做的VBA属性是 Cell.Interior.Color ，但实际上我没有使用宏，而是使用简单的Excel公式。有没有办法用公式来模拟VBA函数？

解决方案

这里有一些小功能。从您的工作表中，按 Alt - F11 以访问VBA编辑器，插入一个新模块，粘贴以下代码，返回到您的工作表并使用它们的名字，如 = FillColor（A1）

前两个是承诺的3 -liners给出字体和背景颜色的十进制值 - 虽然

第二对将十进制数转换为RGB并返回一个格式为N，N， N

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

 函数FillColor（目标为范围）作为变量
 FillColor = Target.Interior.Color 
结束函数
 
函数FontColor（目标为范围）As Variant 
 FontColor = Target.Font.Color 
结束函数
 
函数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）
结束函数
 
函数FontColorRGB（目标作为范围）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）
结束函数
 
函数FillColorRGBArray（目标作为范围）As Variant 
 Dim N As Double，A（3）As整数
 
 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 
结束函数
 
函数FontColorRGBArray（目标为范围）As Variant 
 Dim N作为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 
结束功能
  

谨慎小心：更改单元格的颜色不会通过上述功能/公式开始重新计算，因为一般来说，重新设计单元格不应该推动重新计算。您必须使用 Ctrl + Alt + Shift + F9

Is there, in Excel, a Formula that retrieve the ColorIndex (or RGB) of a cell?

I found the follwing function:

CELL(info_type, the_cell)

documented here, but it does not have any reference information for cell color.

The is a color info, but it's useless for me. In fact, it is described as follows:

"color" The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).

Any idea?

Also, I turned out that the VBA property that do this is Cell.Interior.Color but actually I'm not using Macros, but simple Excel formulas. Is there maybe a way to emulate VBA functions with a formula?

解决方案

Here are some small functions for you. From your sheet, press Alt-F11 to reach the VBA editor, insert a new module, paste the below code, go back to your worksheet and use them by their names, like in =FillColor(A1)

The first two are the promised "3-liners" giving decimal values for font and background colors - not very useful though

The second pair converts the decimal number to RGB and returns a string of format N, N, N

The third pair are array formulas - select 3 cells in a row, enter the formula and press Ctrl+Shift+Enter to obtain numeric RGB values in 3 neighboring cells

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

A word of caution: changing the color of a cell does not start recalculation by the above functions/formulas, as recoloring a cell in general is not supposed to drive recalculation. You have to manually start a full recalculation using Ctrl+Alt+Shift+F9

这篇关于打印单元格颜色的Excel公式（ColorIndex或RGB）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！