How to bring back Cellcolour in to another cell

[fezn]

Hi
I have used hlookup,index and match formula to bring back data which is close to "todays date" but i how do i bring back the cell colour as well? please see attachment?

[mrice]

Private Sub Worksheet_Calculate()
Range("$D$2").Interior.ColorIndex = Rows(2).Find(DateValue(Left(Range("$D$2"), InStr(Range("$D$2"), Chr$(10)) - 1))).Interior.ColorIndex
End Sub

Open up the VBA editor by hitting ALT F11


Paste the macro into the Sheet1

Hit ALT F11 to get back to the worksheet.

[fezn]

Hi Martin

Thanks for that, it worked!

[fezn]

Hi Martin

If i move the data on to row 5, the macro doesnt work, what do I need to change in the macro so that it works in different cells?

Thanks

[FDibbins]

Hi and welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the adress bar)

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[fezn]

Hi.

Thanks for that, I have changed the post title now.

[FDibbins]

Thanks for the title change, and seeing as you still have a question on this, I will mark the thread UNSOLVED for you