Conditional formatting

S

SAGOS

New Member
Joined
Apr 5, 2019
Messages
1
[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]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
Hey, I suppose you could setup a macro like the one I made below, then make a button that prompts the macro on clicking.

Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Conditional format dates in a range which are x days from today
Replies
3
Views
924
Sallyprit
S
Keebler
  • Solved
CELL FORMATTING
Replies
6
Views
255
John_w
J
B
  • Question Question
How to change cells colours when an expiry date is coming to an end
2
Replies
18
Views
1K
Buggy1967
B
rskip
  • Solved
Conditional Format Time Between 2 Cells
Replies
4
Views
389
Fluff
Fluff
G
  • Question Question
Conditional Formatting based on another cell column data
Replies
2
Views
564
gulonraid
G

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top