使用vba创建“色阶”（避免条件格式化）(Creating a “color scale” using vba (avoiding conditional formatting))

 我正在寻找一种方法来通过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之前完成此任务。 

I'm looking for a way to apply a color scale to a set of cells via VBA code but not by applying some conditional formatting... I want to apply them as static colors (InteriorColor)
 
I've searched plenty of excel sites, google and stackoverflow and found nothing :(
 
For my situation if you look at the following picture:
 
 
You can see I've given it a color scale, in this example though I have done the color scale via Conditional formatting. I want to create the color scale via VBA but it must avoid using conditional formatting, I want to assign interior colors to the cells so that the colors are static which makes them visible on all mobile excel viewers, faster, won't change if I was to remove any numbers/rows.
 
Here are some example data Just save it in a csv and open it in excel to see the data in 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
 
I do use the following in python but obviously I can't use this code in VBA, the following code successfully assigns hex colors to the numbers from a predefined array of 50 colors so it's pretty accurate.
 
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()
 
Anyone have any ideas or has anyone done this before with VBA.

原文：https://stackoverflow.com/questions/28217226