find specific word in cell

**Abroza** · 10-26-2009, 04:03 AM

Hey,
I need a vba code that will find all the columns (j) which there cell(1,j) includes the word "Date".
the word "date" will be a part of a title, for exmple: "due date", "starting date"
thanks

**Richard Schollar** · 10-26-2009, 04:36 AM

Hi

Here's some code you could use:

Please Login or Register  to view this content.

Richard

**Abroza** · 10-26-2009, 04:49 AM

that works great, maybe you can help further more...
I want to continue this code, and after the code findes a column with the word date ( let say column j) , i want to go over all of column j (for row 2 up untill the last row which filled with text) and color in red all the dates that in between [(today+7)-(today)]
meaning all the dates in the upcoming week. I wany to color only those cells, no need to color in red the "date" cell as befor.
any chance you can help me?
thanks alot

**Richard Schollar** · 10-27-2009, 05:43 AM

You could do this using Conditional FOrmatting (it would make it a lot simpler than using code) and this would then automatically update each day:

1. Select your table data range (for the sake of argument let's assume that it is A1:H1000)
2. On the Home ribbon select Conditional FOrmatting then New Rule
3. On the New Formatting Rule dialog select "Use a formula to determine which cells to format"
4. Type in the following formula:

=SEARCH("Date",A$1)*(A1>=TODAY())*(A1<=TODAY()+7)

becareful you copy it exactly as it appears above
5. Hit the Format button and apply your Red fill. Click OK.

Richard