Find out the minimum interior colorindex in each column

[Ann_BBO]

Hi All,

I have a series column data. For each column, i would like to find out the minimum colorindex. See the attached file.

Example.
In Column B, all column cells are None of interior colorindex so the result shown in B15 = None Interior colorindex.
In Column C, it have a Orange interior colorindex (=45) in one cell so the result are shown in C15 = Orange interior colorindex.
In Column D, it have a Purple interior colorindex (=7) in one cell so the result are shown in D15= Purple interior colorindex.
In Column H, it have a Red, Purple, Orange interior colorindex in one of the column cells respectively. As the red colorindex are the minumum index (=3) and so the H15=Red Interior colorindex.

Is using the excel format function or need to write the VBA code to achieve this goal?

Thanks
Ann

[Ann_BBO]

I have doen the above task. However, it create another problem to me.

If the interior colorindex is come from Conditional formatting, I found that the actual cell will not have the actual corrsponding interior colorindex even through we see the interior colorindex (e.g orange color) in the cell. How to solve it? Thx

[Ann_BBO]

Here is the code for the cells have the cell interior colorindex (but not the conditional formatting)
Sub FindColorIndex()
Dim r As Integer
Dim c As Integer
Dim previouscolor
Dim currentcolor
c = 1
r = 4
For Each Cell In Range(Cells(4, c), Cells(4, 20))
previouscolor = 9999
Do
If Cells(r, c).Interior.ColorIndex = xlNone Then
currentcolor = 9999
Else
currentcolor = Cells(r, c).Interior.ColorIndex
End If

If currentcolor < previouscolor Then
previouscolor = currentcolor
Else
previoscolor = previouscolor
End If
r = r + 1
Loop Until r = Cells(Rows.Count, c).End(xlUp).Row + 1
If previouscolor = 9999 Then
Cells(Cells(Rows.Count, c).End(xlUp).Row + 2, c).Interior.ColorIndex = xlNone
Else
Cells(Cells(Rows.Count, c).End(xlUp).Row + 2, c).Interior.ColorIndex = previouscolor
End If

c = c + 1
r = 4
Next
End Sub



If the interior colorindex is come from Conditional formatting, how can i modify this code??

Thanks
Ann

[mikerickson]

If the color comes from Conditional Formatting, don't search for color, search for the conditions that cause the color.

For example if =0 is what causes Red. It's difficult to count Red cells, much easier to count cells that =0.