我正在寻找一种方法通过VBA代码将颜色标度应用于一组单元格,但不是通过应用一些条件格式...我想将它们应用为静态颜色(InteriorColor)
我搜索了很多excel网站,谷歌和stackoverflow,没有发现任何东西:(
对于我的情况,如果你看下面的图片:
你可以看到我给它一个色标,在这个例子中虽然我已经通过条件格式化完成了色阶.我想通过VBA创建色阶,但它必须避免使用条件格式,我想为单元格指定内部颜色,以便颜色是静态的,这使得它们在所有移动excel查看器上都可见,速度更快,如果我不会改变是删除任何数字/行.
下面是一些示例数据只需将其保存在csv中并在excel中打开它以查看excel中的数据:P:
Data 1 (Yes there are blanks),Data 2,Data 3,Data 4,Data 5,Data 6
155.7321504,144.6395913,1,-4,-9.3844,0.255813953
113.0646481,120.1609771,5,-2,-2.5874,0.088082902
126.7759917,125.3691519,2,0,-0.0004,0.107843137
,0,7,,,0.035714286
123.0716084,118.0409686,4,0,0.3236,0.118881119
132.4137536,126.5740362,3,-2,-3.8814,0.090909091
70,105.9874422,6,-1,-0.3234,0.103896104
我在python中使用以下内容,但显然我无法在VBA中使用此代码,以下代码成功地将十六进制颜色分配给50种颜色的预定义数组中的数字,因此它非常准确.
def mapValues(values):
nValues = np.asarray(values, dtype="|S8")
mask = (nValues != '')
maskedValues = [float(i.split('%')[0]) for i in nValues[mask]]
colorMap = np.array(['#F8696B', '#F86E6C', '#F8736D', '#F8786E', '#F97E6F', '#F98370', '#F98871', '#FA8E72', '#FA9373', '#FA9874', '#FA9E75', '#FBA376', '#FBA877', '#FBAD78', '#FCB379', '#FCB87A', '#FCBD7B', '#FCC37C', '#FDC87D', '#FDCD7E', '#FDD37F', '#FED880', '#FEDD81', '#FEE382', '#FEE883', '#FCEB84', '#F6E984', '#F0E784', '#E9E583', '#E3E383', '#DCE182', '#D6E082', '#D0DE82', '#C9DC81', '#C3DA81', '#BDD881', '#B6D680', '#B0D580', '#AAD380', '#A3D17F', '#9DCF7F', '#96CD7E', '#90CB7E', '#8ACA7E', '#83C87D', '#7DC67D', '#77C47D', '#70C27C', '#6AC07C', '#63BE7B'])
_, bins = np.histogram(maskedValues, 49)
try:
mapped = np.digitize(maskedValues, bins)
except:
mapped = int(0)
nValues[mask] = colorMap[mapped - 1]
nValues[~mask] = "#808080"
return nValues.tolist()
任何人都有任何想法或任何人在使用VBA之前完成此任务.
1> Paul Kelly..：
以下函数CalcColorScale将返回给定任意两种颜色和比例的颜色.比例是当前数据相对于数据范围的值.例如,如果您的数据是0到200,那么数据值100将是50%(.5)
图像显示红色和蓝色之间的缩放结果
Public Sub Test()
' Sets cell A1 to background purple
Sheet1.Range("A1").Interior.Color = CalcColorScale(rgbRed, rgbBlue, 0.5)
End Sub
' color1: The starting color as a long
' color2: The end color as a long
' dScale: This is the percentage in decimal of the color.
Public Function CalcColorScale(color1 As Long, color2 As Long, dScale As Double) As Long
' Convert the colors to red, green, blue components
Dim r1 As Long, g1 As Long, b1 As Long
r1 = color1 Mod 256
g1 = (color1 \ 256) Mod 256
b1 = (color1 \ 256 \ 256) Mod 256
Dim r2 As Long, g2 As Long, b2 As Long
r2 = color2 Mod 256
g2 = (color2 \ 256) Mod 256
b2 = (color2 \ 256 \ 256) Mod 256
CalcColorScale = RGB(CalcColorScaleRGB(r1, r2, dScale) _
, CalcColorScaleRGB(g1, g2, dScale) _
, CalcColorScaleRGB(b1, b2, dScale))
End Function
' Calculates the R,G or B for a color between two colors based the percentage between them
' e.g .5 would be halfway between the two colors
Public Function CalcColorScaleRGB(color1 As Long, color2 As Long, dScale As Double) As Long
If color2 color1 Then
CalcColorScaleRGB = color1 + (Abs(color1 - color2) * dScale)
Else
CalcColorScaleRGB = color1
End If
End Function
哦,伙计,我刚刚完成了我工作超过半小时的完全相同的概念(只是不那么漂亮),现在我觉得无用:D你得到我的投票,我甚至不知道你能做到这一点" b1 =(color1\256\256)Mod 256"得到RGB分量,很酷.
我提供的代码将返回基于比例的颜色.您需要确定您的比例.在您的示例中,单元格A2:A8的范围为13到38.因此,单元格A2(值为32)的比例大约为0.75.您需要遍历每个单元格并根据该单元格中的值传递CalcColorScale适当的范围.
@PaulKelly,很棒的解决方案,但它需要一些测试(你得到我的+1).@Ryflex,有可能获得3个色标......在任何列>> 0到100步骤5中为每个单元格设置数值>>"如果单元格<50那么:Cell.Interior.Color = CalcColorScale (vbRed,vbYellow,细胞/ 50):否则:Cell.Interior.Color = CalcColorScale(vbYellow,vbGreen,(小区 - 50)/ 50):结束If`结果我已经从红色(0)>>通过颜色黄色(50)>>到绿色(100).
京公网安备 11010802041100号