まずは昼飯だ

甕つくりの焼酎と刺身

1

at Exlsm

Sub SerialNumberForPPT()
'パワポのスライド番号をPrimary climate-related risk driverの列の右隣に設定
For tgl_ws = 2 To 3
'Primary climate-related risk driverの列の右隣に列を挿入
Dim this_ws As Worksheet: Set this_ws = ThisWorkbook.Worksheets(tgl_ws)

Dim pcr As Range
Set pcr = this_ws.Rows(1).Find(what:="Primary climate-related risk driver")
pcr_col = pcr.Column

Dim max_row As Integer: max_row = this_ws.Range("A1").CurrentRegion.Rows.Count

'列の挿入
Columns(pcr_col + 1).Insert
this_ws.Cells(1, pcr_col + 1).Value = "パワポのスライドナンバー"

Dim prefix As String: prefix = "Slide no. "
Dim m, serial_number As Integer: m = 2: serial_number = 1
For n = 2 To max_row

str1 = this_ws.Cells(m, pcr_col).Value
str2 = this_ws.Cells(n, pcr_col).Value

If str1 = str2 Then

this_ws.Cells(n, pcr_col + 1).Value = prefix & serial_number

Else

serial_number = serial_number + 1
m = n
this_ws.Cells(n, pcr_col + 1).Value = prefix & serial_number

End If

Next n

Next tgl_ws
End Sub

Sub for_trim()

Dim this_ws As Worksheet: Set this_ws = ThisWorkbook.Worksheets(3)

Dim pcr As Range
Set pcr = this_ws.Rows(1).Find(what:="Primary climate-related risk driver")
pcr_col = pcr.Column

Dim max_row As Integer: max_row = this_ws.Range("A1").CurrentRegion.Rows.Count

For i = 2 To max_row
this_ws.Cells(i, pcr_col).Value = RTrim(this_ws.Cells(i, pcr_col).Value)
Next i
End Sub

at exlsm

Sub set_color() 'リスクドライバー(スライド番号)ごとに色付け
For tgl_ws = 2 To 3

Dim this_ws As Worksheet: Set this_ws = ThisWorkbook.Worksheets(tgl_ws)
Dim pcr As Range
Set pcr = this_ws.Rows(1).Find(what:="Primary climate-related risk driver")
pcr_col = pcr.Column

Dim max_row As Integer: max_row = this_ws.Range("A1").CurrentRegion.Rows.Count

this_ws.Cells.Interior.ColorIndex = 0
For i = 2 To max_row
Dim str As Integer: str = Right(this_ws.Cells(i, pcr_col + 1).Value, Len(this_ws.Cells(i, pcr_col + 1).Value) - 10)

If str Mod 2 = 0 Then
With this_ws.Rows(i)
.Interior.ThemeColor = xlThemeColorAccent5
.Interior.TintAndShade = 0.8
End With
End If

Next i





Next tgl_ws
End Sub