Conditional formatting

[SAGOS]

[TABLE="width: 1044"]
<colgroup><col><col><col><col span="17"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]I need some help

I have a training spread sheet. On the left i have a list of names. The the right of the names i have a series of columns [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]that have various headers such as first aid, fire marshal. Ive formatted each cell so i can input the date they took the exam and it calculates if the certificate has expired. The cell turns red or green depending on if it has expired on not using a =today() system. (THE CELL, NOT THE TEXT)

At the top of each column i have a cell names 'compliant'.

What i need is the cell 'compliant' to turn red if any of the cells bellow it are red. Indicating that someone in that column has expired. [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]First aid[/TD]
[TD]Fire marshal[/TD]
[TD]Cranes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]COMPLIANT[/TD]
[TD]COMPLIANT[/TD]
[TD]COMPLIANT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John smith[/TD]
[TD]01/01/2017[/TD]
[TD]01/01/2017[/TD]
[TD]01/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bob smith[/TD]
[TD]01/01/2017[/TD]
[TD]01/01/2017[/TD]
[TD]01/01/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jenny smith[/TD]
[TD]01/01/2016[/TD]
[TD]01/01/2017[/TD]
[TD]01/01/2017[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][TABLE="width: 1044"]
<colgroup><col><col><col><col span="17"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

if you can push down one row, then use =MIN() for the column and CF that.

Update, you either need to use Aggregate and ignore blanks, or use a formula for only those greater than zero

 

[tyija1995]

Hey, I suppose you could setup a macro like the one I made below, then make a button that prompts the macro on clicking.

Sub HighlightRed()
    Dim i As Long, j As Long, rowNums As Long, colNums As Long
    
    colNums = Cells(1, Columns.Count).End(xlToLeft).Column
    For j = 2 To colNums
        rowNums = Cells(Rows.Count, j).End(xlUp).Row
        For i = 3 To rowNums
            If Cells(i, j).DisplayFormat.Interior.ColorIndex = 3 Then
                Cells(2, j).Interior.ColorIndex = 3
            End If
        Next i
    Next j
    
End Sub

Providing that you have already setup the dates to turn red if expired, then the macro prompts the 2nd row of the looped column to turn red.