Average color in Excel

May 26, 2009 at 04:29:37
Specs: Windows XP
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??

See More: Average color in Excel

Report •

May 26, 2009 at 08:17:16
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
'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 " & _
'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

Report •
Related Solutions

Ask Question