» Set the Background Color according to the date using VBA in Microsoft Excel
CATEGORY - General Topics in VBA
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- Quantitative Methods in Derivatives Pricing: An Introduction to Computational Finance
- East of Eden (Oprah's Book Club)
- AWAKEN THE GIANT WITHIN : HOW TO TAKE IMMEDIATE CONTROL OF YOUR MENTAL, EMOTIONAL, PHYSICAL AND FINANCIAL
- Microsoft Access 2002 for Dummies
- Microsoft Windows XP Registry Guide
- MP Managerial Accounting w/ Topic Tackler, Net Tutor, & PowerWeb
to change the background color
arun sharma
Set the Background Color according to the date using VBA in Microsoft Excel
good one
kiran
For quick support this site in very much useful
excellent
gyan
this web site is so simple along with finding out the usefull information in vbe

