VBA question regarding changing column border

[randomString]

Hi,

I am currently creating a spreadsheet on Excel 2003 and need to conditionally format some data. I have already used all 3 conditions for this particular range so am trying to use VBA to do my final formatting.

Essentially I have some data organized into columns by week (date format). I want to change the border of that column to red if the look-up date falls within that week. The column's date is in row 5 of the current column and is the first day of that week.

I don't know much VBA so I have cobbled together something that probably has large errors in it.

Thanks for any help you can all offer!

---


Here is what I have (put together piecemeal from various sources):


Private Sub Workbook_Open()

Set DataRange = Range("k6:bf66")
For Each Col In DataRange

Dim columnDate As Date
Dim todaysDate As Date
Dim nextWeeksDate As Date

columnDate = Range(Col.Column & "5").Value
todaysDate = Range(f1).Value
nextWeeksDate = todaysDate + 7

If todaysDate >= columnDate And todaysDate < nextWeeksDate Then

Range(lookUpCell).Borders(xlEdgeLeft).LineStyle = xlContinuous
Range(lookUpCell).Borders(xlEdgeLeft).Color = vbRed

Range(lookUpCell).Borders(xlEdgeRight).LineStyle = xlContinuous
Range(lookUpCell).Borders(xlEdgeRight).Color = vbRed

End If

Next
End Sub