Apr 06 2020 09:22 AM
Hi
We have a piece of software that outputs an Excel file once a week, this file has to be organised and edited to show specific information, which is quite a tedious task!
I am trying to produce a macro that will do everything for me, and have almost got everything figured out except the last little bit.
I need it to highlight all rows that have a date before today in column E
This bit of code i have been trying sort of works:
Dim TDateM As String
TDateM = Date
endrow = Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Range("E2:E" & endrow)
If cell.Value < TDateM Then
cell.EntireRow.Interior.ColorIndex = 6
End If
Next
It has two small problems though, firstly it only highlights the rows prior to today for this month, anything last month or previous it doesn't seem to work for. This is a bit odd and i don't really understand why it would do this.
Secondly, it highlights the row of the whole workbook, i really want it to only highlight the row in the table. (Columns A to T if that helps)
Anyone able to help?
Ta muchly
Apr 06 2020 10:05 AM
Apr 06 2020 10:17 AM
@mtarler
Thanks, that seems to have at least get it highlighting just the table.
The are no breaks in the data, other than they are not consecutive.
Quick screen shot of the data:
Conditional formatting won't work as it is a new spreadsheet that is generated every week and has to be altered to produce a report.
Apr 06 2020 10:29 AM
Apr 06 2020 10:50 AM
@mtarler
That is a good point, but to be honest i haven't a clue how to resolve it.
Apr 06 2020 11:05 AM - edited Apr 06 2020 11:05 AM
@TattyJJ in VBA editor there is an "Immediate Window" that I love to test and try things. If it isn't already up you can find it under the View tab or hit Ctrl-G.
In the window type something like: Print isdate(range("e20").value)
Apr 06 2020 11:13 AM
Apr 06 2020 11:14 AM
@mtarler
This has got me quite stumped! So by changing TDateM = Date to TDateM = Date + 12 it still works fine until it gets to a different month. If it was some sort of confusion between date formats this should have caused it to trip up?
Print isdate(range("e20").value) comes back as True for all the cells i tried in column E
Apr 06 2020 11:42 AM
@mtarler
GOT IT!!!
By converting all the dates in column E to number format, then using CLng(Date) to do the comparison with all the dates as integers, then turning them back into dates.
Seems a bit long winded, but it does the job...
Thanks for your help!