Hello,

I had a report, which needs to be colored based on the average value of cells. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow

Now based on the color of the rows cells , the header row needs to be colored. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones.

For the first part, i colored the cells with conditional formatting, for second one i wrote a macro code. But some how the header row is not getting color as expected, even though the color index used in conditional formatting and code are similar. I am pasting the code .

ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-7]:RC[-1])"
    Range("L13").Select
    Selection.AutoFill Destination:=Range("L13:L144")
    Range("L13:L144").Select
    Columns("L:L").Select
    Selection.NumberFormat = "0"
    Range("E13:K144").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$L13 * 0,2", Formula2:="=$L13 * 0,5"
    Selection.FormatConditions(1).Interior.ColorIndex = 6
    
    Dim R As Integer
    Dim Y As Integer
    
    For Each C In Range("K13:K144")
    If C.Interior.ColorIndex = 3 Then R = R + 1
    If C.Interior.ColorIndex = 6 Then Y = Y + 1
    
    Next C
    
    If R > 0 Then
    Range("K11").Interior.ColorIndex = 3
    ElseIf Y > 0 Then
    Range("K11").Interior.ColorIndex = 6
    Else
    Range("K11").Interior.ColorIndex = 4
    End If
    
        R = 0: Y = 0:
        
         For Each C In Range("E13:E144")
    If C.Interior.ColorIndex = 3 Then R = R + 1
    If C.Interior.ColorIndex = 6 Then Y = Y + 1
    
    Next C
    
    If R > 0 Then
    Range("E11").Interior.ColorIndex = 3
    ElseIf Y > 0 Then
    Range("E11").Interior.ColorIndex = 6
    Else
    Range("E11").Interior.ColorIndex = 4
    End If
    
        R = 0: Y = 0:

Configuration: Windows XP
Internet Explorer 7.0