Good Afternoon,
In the attached spreadsheet, I want the conditional formatting to highlight any cell in column C if the date is prior to today.
Things to notice:
1-Some cells in column C have text with the date
2-Some cells in column C have multiple dates
3-If there is any text with the date, it will always be in the format of "date-text" and date doesn't have leading zeros for single digit months or days
Any assistance in this matter is greatly appreciated.
In my opinion, it's better to use VBA to solve the problem.
Check my solution.
Please Login or Register to view this content.
Best Regards,
Maras.
With this:
5/1/2021-test message 1; 10/15/2020-test message 2
Is the date alway "month/date/year"?
how many date-strings possible exist in text? 2 or more?
Quang PT
I like it! Thank you. What would be the code I would add to the worksheet object if I wanted this macro run automatically when there is a selection change only in that column? I know to use "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" but not sure how to create the rest of the code to run if there is a selection change only for that column.
Check.
Put the following code in the 'Sheet1' module.Insert this code into a normal VBA module.Please Login or Register to view this content.Please Login or Register to view this content.
Yes, however date could be single digit month and single digit day and two digit year (ex. 1/1/20).
Clarification: I would make a change to the field in column C but would like the highlighting to update when I click outside that cell I just updated (outside cell could be in column C or any other column or row). Would you please update the code to reflect this? Thank you.
Last edited by KolKon; 11-05-2020 at 12:20 PM.
Please try CF formula
=OR(FILTERXML("<x><m>"&SUBSTITUTE(SUBSTITUTE(C2,";","-"),"-","</m><m>")&"</m></x>","//m")<TODAY())
This is great! Thank you all for your help!
Maras,
Did you happen to have a chance to review my latest message about the clarification on 11/5/2020 regarding clicking on a different cell? I appreciate your help.
If I understand you right, just change thatonPlease Login or Register to view this content..Please Login or Register to view this content.
This is perfect! Thank you!
I need to make a change to this request. If there is any text with the date, it could be in any of the following formats:
date-text
OR
date (text); date-text; date (text) etc...basically could be a parenthesis after the date instead of a dash
Is it possible to allow for parenthesis in this formula?
Thank you.
Last edited by KolKon; 03-24-2021 at 02:15 PM.
What is your problem?
Show in the attachment before and expected result.
Please see the attached workbook with examples. Thank you.
The problem is with the date format.
In my Excel, dates are only in the format 'dd-mm-yyyy' and yours may be 'mm/dd/yyyy'.
Check in the attachment.
I see...Please let me know how to update the VBA for the following using the attached version 3 workbook:
1-Change it to be based on column N (and what would I have to change in the future if I added more columns to the left)?
2-Execute as soon as I update and click off cell in column N
Thanks.
In the VBA module of the 'Sheet1'.In a normal module of VBA.Please Login or Register to view this content.Please Login or Register to view this content.
I'm sorry...I should have also specified I also want this to run when the workbook opens. In addition, the date format could be mm/dd/yy instead of mm/dd/yyyy. Would you please edit the VBA to accommodate both issues.
Thank you.
I think this does what you want.
It's perfect...one last thing.
I would like the font color to be white. I tried "If dt < Date Then c.Interior.Color = vbRed and c.font.color = vbWhite", but nope. I also tried moving the white to next line, but nope there too.
Then
c.Interior.Color = vbRed
c.font.color = vbWhite
end if
Perfect! Thank you so much!
I think it should be like that in the procedure 'Formating (c As Range)'.Please Login or Register to view this content.
This is great...thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks