» Set the Background Color according to the date using VBA in Microsoft Excel

Question:
How can I change the BackgroundColour of a Date-series that must be set?


Answer:
Enter the following Code with XL5/7 in an English-language, with XL8 in a general Module, save, close, and open the Workbook.

'Place the code below into the This Workbook module





Private Sub Workbook_Open() 

   Worksheets("Sheet1").OnEntry = "Marking"

End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Worksheets("Sheet1").OnEntry = ""

End Sub



'Place the code below into the standard module





Sub Marking()

    Dim AC As Range, B As Range

    Dim Start As Range, Off As Range

    Set AC = Application.Caller

    If AC.Column > 2 Then Exit Sub

    If AC.Column = 1 Then

        If IsEmpty(AC.Offset(0, 1)) Then Exit Sub

    Else

        If IsEmpty(AC.Offset(0, -1)) Then Exit Sub

    End If

    Set B = Range(Cells(1, 3), Cells(1, 256))

    Set Start = B.Find(Cells(AC.Row, 1).Value, LookAt:=xlWhole)

    Set Off = B.Find(Cells(AC.Row, 2).Value, LookAt:=xlWhole)

    Range(Cells(AC.Row, Start.Column), Cells(AC.Row, Off.Column)) _

        .Interior.ColorIndex = 3

    If Start.Column > 3 Then

        Range(Cells(AC.Row, 3), Cells(AC.Row, Start.Column - 1)) _

            .Interior.ColorIndex = xlNone

    End If

    If Off.Column < 256 Then

        Range(Cells(AC.Row, Off.Column + 1), Cells(AC.Row, 256)) _

            .Interior.ColorIndex = xlNone

    End If

End Sub