Sub Auto_Open() '開啟檔案自動執行
'Debug.Print Cells(1, 8).Value
Call cal_date
End Sub
Sub cal_date()
'計算權證到期日天數
' 1.抓今天日期 2.日期相減
'Debug.Print Date
Dim my_cols As Integer
Cells(1, 8).Value = Date
day1 = Date
day2 = Cells(9, 8).Value
Cells.Select
my_rows = Sheets(1).UsedRange.Rows.Count '抓有資料之最大列號
my_cols = Sheets(1).UsedRange.Columns.Count '抓有資料之最大行號
k = 2
'For i = 2 To 9
For i = 2 To my_rows
If Cells(i, 8).Value <> "" Then
day2 = Cells(i, 8).Value
Cells(i, 9).Value = day2 - day1
If Cells(i, 9).Value < 110 Then
my_num = ConvertToLetter(my_cols) '將抓到的欄位數字轉為欄位名稱，如：K，否則出現錯誤
Range("A" & i & ":" & my_num & i & "").Select '組合成選取之範圍，如：A9:K9
With Selection.Font '標示紅字
.Color = -16776961
.TintAndShade = 0
End With
End If
End If
Next i
'Columns(4).ColumnWidth = 6
ActiveCell.Select
ActiveWorkbook.Save
End Sub
Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function
留言列表
