Excel help

[earwig999]

I'm struggling with Excel.

I want to basically hide rows that contain a date >TODAY, which I have managed just about.


but there are different dates in different columns within the same row, so at the minute it is only looking at the first column. How can I make it check the rest of the ranges?

e.g.
A B C D E F G H
1 Forename Surname Region D.O.B Competency Expiry Date Competency Expiry Date

2 Joe Bloggs Midlands 01/04/1980 First Aid 10/01/2014 CSCS 11/12/2012
3 Sam Snotty London 10/06/1969 CSCS 01/04/2012 NVQ Level 2 09/08/2013

Currently my Macro is hiding Joe Bloggs row 2, but not the Sam Snotty row as I'm not sure how to make it check column F AND H as well.

 

[leemoo]

So the formula that you have cannot you not put the column letter and row number in to that so it looks at the particular cell or cells.

 

[Him Her]

Why not do it all with a formula?

Hide the entire date column, add a column to the right and fill the cells with the formula =IF(A1>TODAY(),"",A1) substituting your date cell for the A1?

Copying down the column will change the A1 accordingly...

 

[earwig999]

=IF(A1>TODAY(),"",A1) won't work in VB macro as TODAY is not a function. I have to use DATE (Don't know why) I can use that for individual cells, but not as a macro to hide those rows.

 

[Him Her]

My point, you don't need the macro?

 

[Abu Baniaz]

I normally hide a few rows at the top for notes and other formulas.
Make a cells which has a date. Let us say A1.

IF(A6>$A$1,"",A6)

 

[earwig999]

My point, you don't need the macro?

I do or they will be hidden all the time and not able to hide/unhide at the press of a button. ?

I need to be able to make a check based on the same criteria, but on different ranges and if the date is >TODAY in any of those columns then hide the row. So is should only show the rows which contain dates <TODAY (Overdue/Expired)

It's a bit difficult to explain without seeing it.

 

[Him Her]

Then configure it as a database and change the selection criteria would be easiest surely?

 

[earwig999]

Then configure it as a database and change the selection criteria would be easiest surely?

I don't know how or why, that's why I'm asking. I could only think of it this way.

I have already set up a formula using conditional formatting to create a traffic light system to fill the cell with either red, amber or green based on the expiry date distance from today.

 

[earwig999]

Here is the file

The macro I have assigned to the button - 'Only Show Expired' is the one I need to tweak. It currently only checks column F, but I need it to check H, J, L etc. as well

 

[Him Her]

Leave it with me - man-flu and Merlot lol

 

[earwig999]

Leave it with me - man-flu and Merlot lol

It's still a work in progress so not fully functional yet. The last time I did VB I used to be a 32" waist and now I'm a 42" so I may have lost a bit somewhere in time.

 

[Him Her]

It's still a work in progress so not fully functional yet. The last time I did VB I used to be a 32" waist and now I'm a 42" so I may have lost a bit somewhere in time.

So, you need to automatically clunk through the sheet marking/formatting according to date?

 

[earwig999]

The formatting should be done via conditional formatting so will do it automatically, but to filter out only the expired competencies needs to be run from a button. Obviously there are only a few dummy rows in for testing purposes, but eventually it will hold 800 people, so I want to make it as simple to filter out as possible.

 

[Him Her]

Assuming I understood correctly, the following code should get you well on the way...


Sub CheckExpired()

Dim myDate As Date
'format the date excluding time
myDate = FormatDateTime(Now, 2)

ActiveWorkbook.Sheets("Matrix").Activate

'Find out how many rows to check
lRow = Range("F" & Rows.Count).End(xlUp).Row
Set MR = Range("F6:F" & lRow)
'For each cell in column F
For Each cell In MR
'If the date is greater than today change the fill colour to Red
If FormatDateTime(cell.Value, 2) > myDate Then cell.Interior.ColorIndex = 3
'If the date is greater than today hide the entire row
'If FormatDateTime(cell.Value, 2) > myDate Then cell.EntireRow.Hidden = True
Next

'Repeat for other columns
lRow = Range("H" & Rows.Count).End(xlUp).Row
Set MR = Range("H6:H" & lRow)
For Each cell In MR
If FormatDateTime(cell.Value, 2) > myDate Then cell.Interior.ColorIndex = 3
'If FormatDateTime(cell.Value, 2) > myDate Then cell.EntireRow.Hidden = True
Next

End Sub

 

[earwig999]

I'll try that at work on Monday, but for now I did a simple logic test at the end of each row and made a formula based on true/false decided by the logic test. Only trouble is it has added a few additional columns, but I've hidden them.