Need Macro help!

[mocephur]

Excel gurus,

I need help writing a macro.

I have data in cells for column A....the data looks like this......"Review in 10 - 12/22/21"

I would like a macro that looks at the cells and compare the "Review in ?" value to the date listed....if it's 10 business days or over it turns the cell RED. If there is no data in the cells for column A it disregards.

The "Review in ?" can have any value....1 to 30.

Can anyone help me?

 

[JamesCanale]

Does it have to be a macro? Some nice conditional formatting formulas can do the trick. This highlights in RED anything where TODAY is more than X days after the date listed. You might want to link to an array of holidays in the WORKDAYS formula.

MrExcelPlayground6.xlsx
	H
2	Review in 10 - 12/22/21
3	Review in 5 - 12/25/21
4	Review in 10 - 1/2/22
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:H4	Expression	=TODAY()>WORKDAY.INTL(VALUE(RIGHT(H2,LEN(H2)-SEARCH("-",H2))),VALUE(LEFT(RIGHT(H2,LEN(H2)-10),SEARCH("-",RIGHT(H2,LEN(H2)-10))-1)),1)	text	NO

 

[mocephur]

H

I didn't even think of conditional formatting...nice!
If my data is in column A...i just need to change your H's to A's correct?

Next...do i do a new rule in conditional formatting?

 

[JamesCanale]

CHanges all of the H's to A's, select A2 and conditional formatting- by formula, then paste that in there, and then customize the formatting. Then you can right-click on A2, and drag down 'formatting only'.

Or you select the whole range that starts in A2, and conditional formatting then...
If the second row isn't right, change the 2 to something else.

 

[mocephur]

ok....i see my problem.....i forgot to mention that after the date there can be more info

Review in 1 - 12/30/21 - xxxxxxxx - xxxxxxxxxxxx and so on.....the data after the date isn't fixed, it's just whatever the user enters

Can we make that work?

 

[JamesCanale]

It gets weirder

MrExcelPlayground6.xlsx
	H
8	Review in 10 - 12/22/21 - jkfasdlj - asjdflkas
9	Review in 5 - 12/25/21 - dsfjlaskd
10	Review in 10 - 1/2/22 - jsldkjfal
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H8:H10	Expression	=TODAY()>WORKDAY.INTL(VALUE(MID(H8,SEARCH("-",H8)+2,SEARCH("-",H8,1+SEARCH("-",H8))-SEARCH("-",H8)-2)),VALUE(LEFT(RIGHT(H8,LEN(H8)-10),SEARCH("-",RIGHT(H8,LEN(H8)-10))-1)),1)	text	NO

 

[mocephur]

Dang....no joy!

I guess there is too much data past the date.