スポンサーサイト

--–--–-- (--) --:--
上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

最新PDF

2018–03–01 (Thu) 19:21
久しぶりの更新は、最新PDFを拾ってくるマクロです。
メールをするときにSCANフォルダの起動→名前の変更→添付が面倒だったので省いてしまいました♪

Sub 最新PDF()

Dim strFileName As String
Dim strFilePath As String

strFilePath = "C:\Scan\"
strFileName = fncGetNewFile(strFilePath)

If strFileName = "" Then
MsgBox "ファイルがありません。"
Exit Sub
End If

Dim x As Long
x = 1
Do Until Worksheets("メールpdf").Cells(x, 1) = ""
x = x + 1
Loop
Dim namae As String
namae = InputBox("名前を入力して下さい")
Name strFilePath & strFileName As strFilePath & namae & ".pdf"
Worksheets("メールpdf").Cells(x, 1) = strFilePath & namae & ".pdf"
namae = InputBox("ファイル内容を入力して下さい")
Worksheets("メールpdf").Cells(x, 2) = namae
Worksheets("メールpdf").Cells(x, 2).Copy
End Sub


Public Function fncGetNewFile(strFilePath As String) As String

Dim strFileName As String
Dim tmpFileName As String
Dim time As Date
Dim pretime As Date

tmpFileName = Dir(strFilePath & "*.pdf")

Do Until tmpFileName = ""
pretime = FileDateTime(strFilePath & tmpFileName)
If pretime > time Then
time = pretime
strFileName = tmpFileName
End If
tmpFileName = Dir()
Loop

fncGetNewFile = strFileName

End Function
スポンサーサイト

選択したシートの式を削除する

2018–01–10 (Wed) 23:18
Sub 複数シート()
On Error Resume Next
Application.ScreenUpdating = False
Dim sh As Object
Dim kazu As Long
kazu = ActiveWindow.SelectedSheets.Count
Dim ichi As Variant
Dim i As Long
Dim MaxRow As Long
Dim MaxCol As Long
i = 0
ReDim ichi(kazu - 1)
For Each sh In ActiveWindow.SelectedSheets
ichi(i) = sh.Name
i = i + 1

Next sh
ActiveWindow.SelectedSheets(1).Select
For i = 0 To UBound(ichi)
MaxRow = 0
MaxCol = 0
Worksheets(ichi(i)).Activate
With Worksheets(ichi(i)).UsedRange
MaxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
MaxCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
End With
Worksheets(ichi(i)).Range("a1", Cells(MaxRow, MaxCol)).Value = Worksheets(ichi(i)).Range("a1", Cells(MaxRow, MaxCol)).Value
Next
Application.ScreenUpdating = True
On Error GoTo 0
End Sub

時間以上で色を変更する

2018–01–09 (Tue) 22:56
Sub 一時間以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""1:00"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 一時間半以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""1:30"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 二時間以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""2:00"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 二時間半以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""2:30"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub


Sub 三時間以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""3:00"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 三時間半以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""3:30"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 四時間以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""4:00"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 四時間半以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""4:30"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 五時間以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""5:00"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 五時間半以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""5:30"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub


Sub 六時間以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""6:00"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 六時間半以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""6:30"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub
Sub 七時間以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""7:00"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 七時間半以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""7:30"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 八時間以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""8:00"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 八時間半以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""8:30"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 九時間以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""9:00"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

Sub 九時間半以上で赤太字にする()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""9:30"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub


百以上で赤太字にする

2018–01–09 (Tue) 22:52
Sub 百以上で赤太字にする()
'
' Macro1 Macro
'

'
Application.WindowState = xlMaximized
Application.WindowState = xlNormal
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=100"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

時間によって色を変えるマクロ

2018–01–03 (Wed) 16:57

Sub 時間指定()
'
' Macro4 Macro
'

'
Application.ScreenUpdating = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=TIMEVALUE(""2:00"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub

 | HOME |  次へ »

プロフィール

はる

Author:はる
FC2ブログへようこそ!

最新記事

最新コメント

フリーエリア

月別アーカイブ

カテゴリ

ブロとも申請フォーム

検索フォーム

RSSリンクの表示

リンク

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。