I have different cells (cell 1-3) with different fill color and I would like to have a separate cell that will have the average fill-color of cell 1-3. For instance if the cell 1 and 2 are yellow, and cell 3 is red, I will have an average color that is similar to orange... but whant t ohave the exact color.

is this possible??

I guess it could be done, but it's going to take some creative thinking on your part. In VBA, each fill color has a Interior.ColorIndex value.

For example, the Interior.ColorIndex value for Red is 3 and for Yellow is 6.

You can set the cells fill color via VBA and you can retrieve the value of a cell's Interior.ColorIndex via VBA. You can even find the average of 2 Interior.ColorIndex values via VBA.

Unfortunately, the average of these 2 values is not going match the Interior.ColorIndex value of the "average color".

For example, if you run the code below, you'll fill B1:B56 with the Fill Colors that match Interior.ColorIndex values from 1 to 56. You will see, both by looking at the sheet and by what the code does, that the average of the Interior.ColrIndex values for Red and Yellow is 4.5. However, looking at the sheet, you'll see that the Interior.ColorIndex value for Orange is 46.

I think the best that you will be able to do is determine the Interior.ColorIndex value you want to use on a case by case basis depending on the average found.

The last piece of code is an example of how that could be done.

Sub FillColors() 'Fill B1:B56 with colors For myColor = 1 To 56 Cells(myColor, 1) = myColor Cells(myColor, 2).Interior.ColorIndex = myColor Next 'Retrieve the Interior.ColorIndex for B3 & B6 MsgBox "Cell B3 has an Interior.ColorIndex of " & _ Range("B3").Interior.ColorIndex & vbCrLf & _ "Cell B6 has an Interior.ColorIndex of " & _ Range("B6").Interior.ColorIndex 'Average the Interior.ColorIndex for B3 & B6 AvgColor = (Range("B3").Interior.ColorIndex + Range("B6").Interior.ColorIndex) / 2 MsgBox "The Average Interior.ColorIndex of B3 & B6 is " & AvgColor End Sub Sub AverageColor() AvgColor = (Range("B3").Interior.ColorIndex + Range("B6").Interior.ColorIndex) / 2 Select Case AvgColor 'Average Red and Yellow = Orange Case 4.5 Range("C1").Interior.ColorIndex = 46 'Average Red and Blue = Purple Case 4 Range("C1").Interior.ColorIndex = 13 End Select End Sub

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History