Microsoft ExcelでVBAを使用して日付に基づいて、フォントの色を変更し

Option Explicit

Sub SetColor()

If IsDate(ActiveCell.Value) And ActiveCell.Value > Date Then

ActiveCell.Interior.Color = RGB(0, 255, 0)

Else

ActiveCell.Interior.Color = RGB(221, 235, 247)

End If

End Sub

Sub ResetColor()

'Declaring variables

Dim Rng, Source As Range

Dim IntRow As Integer, IntCol As Integer

'Specifying all the cells as source range

Set Source = Range("A1", Range("A1").SpecialCells(xlCellTypeLastCell))

'Looping through all the cells

For Each Rng In Source

'Checking whether cell contains a value

If Not IsEmpty(Rng) Then

'Checking whether cell contain value of date data type

If IsDate(Rng.Value) Then

Rng.Select



'Assigning Green color if value is greater than today date

If DateValue(Rng.Value) > Date Then

ActiveCell.Interior.Color = RGB(0, 255, 0)

Else

ActiveCell.Interior.Color = RGB(221, 235, 247)

End If

End If

End If

Next Rng

End Sub

'Insert below code in ThisWorkbook module

Option Explicit

Private Sub Workbook_Open()

With Worksheets("Main")

'Event fired on entry to worksheet

.OnEntry = "SetColor"

'Event fired on sheet activation

.OnSheetActivate = "ResetColor"



End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)



With Worksheets("Main")

.OnEntry = ""

.OnSheetActivate = ""

End With

End Sub