Change cell fill and font color based on date

[lazarandreiflorin]

Hello,

I have a spreadsheet with employees insurance details that include the expiration date. There are a few hundred employees and its hard to keep track. Is there a way to change the cell fill and font color 3 days away from expiration date to make it easier to read. Also to do this without conditional formatting because it is used by many people and they keep using copy and paste and would mess it up. Maybe with some VBA code...I don`t know. Thank you !

 

[mumps]

You could do this with a macro. You could run the macro manually whenever you wanted to check the data. You could have the macro check automatically when you open the file or you have the macro check automatically when the relevant sheet is activated. In any case, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitize

 

[lazarandreiflorin]

Hello,

Sorry for the late reply. Below is the link to the spreadsheet I was talking about. Column K contains the expiry date and based on that I would like the whole row to change fill and font colour when its 3 days away from expiring. And also on column D we type the date of birth of the employee. Is there a formula or macro that calculates the age based on the date of bitrh introduced in the previos cell.
Thank you very much!

TOTAL - IMG Travel Census Tssd ENTERPRISES LTD.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[mumps]

You could run the macro manually whenever you wanted to check the data. You could have the macro check automatically when you open the file or you have the macro check automatically when the relevant sheet is activated.

Which of these options would work best for you?

 

[lazarandreiflorin]

Which of these options would work best for you?

I think the last option would work best. Whenever the sheet is activated.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your TOTAL sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Each time you leave the sheet and then go back to it, the macro will run automatically.

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
    Range("A4").CurrentRegion.AutoFilter Field:=12, Criteria1:=">=" & Date - 3, Operator:=xlAnd, Criteria2:="<=" & Date
    ActiveSheet.AutoFilter.Range.Offset(1).Interior.ColorIndex = 3
    Range("A4").AutoFilter
    Application.ScreenUpdating = True
End Sub