Help us understand the problem. What is going on with this article?

OpenOffice CalcでExcel VBAのマクロを組む(3)

休日セルの色を変える

前回の続き。
曜日セルの表示が土曜日または日曜日になるときはセルの色を変更するようにしてみた。

(前回の記事)
OpenOffice CalcでExcel VBAのマクロを組む(2)
https://qiita.com/hirokichisepg/items/7df5d7f843c862cd7c8e

サンプルマクロ

option vbasupport 1

Sub Main

    REM E列2行目に指定した月末日から日部分だけ取り出す
    Dim lastDay As String
    lastDay = Cells(2, 5)
    lastDay = Right(lastDay, 2)

    Dim dayNum As Integer
    dayNum = CInt(lastDay)

    REM 曜日フラグの設定
    Dim dayFlag  As Integer

    If (StrComp(Cells(1, 6), "月", vbTextCompare ) = 0) Then
        REM 月初日が月曜日
        dayFlag = 1
    ElseIf (StrComp(Cells(1, 6), "火", vbTextCompare ) = 0) Then
        REM 月初日が火曜日
        dayFlag = 2
    ElseIf (StrComp(Cells(1, 6), "水", vbTextCompare ) = 0) Then
        REM 月初日が水曜日
         dayFlag = 3
    ElseIf (StrComp(Cells(1, 6), "木", vbTextCompare ) = 0) Then
        REM 月初日が木曜日
        dayFlag = 4
    ElseIf (StrComp(Cells(1, 6), "金", vbTextCompare ) = 0) Then
        REM 月初日が金曜日
        dayFlag = 5
    ElseIf (StrComp(Cells(1, 6), "土", vbTextCompare ) = 0) Then
        REM 月初日が土曜日
        dayFlag = 6
    Else
        REM 月初日が日曜日
        dayFlag = 7
    End If

    Dim dayCnt As Integer
    Dim rangeVal As String
    For dayCnt = 1 To 31
        If (dayCnt <= dayNum) Then
            REM 月末日までの日をA列の3行目から順に記入する
            Cells(dayCnt + 2, 1) = dayCnt

            REM 曜日を記入する
            Select Case dayFlag
            Case 1
                Cells(dayCnt + 2, 2) = "月"
                SetCellColor(dayCnt)
            Case 2
                Cells(dayCnt + 2, 2) = "火"
                SetCellColor(dayCnt)
            Case 3
                Cells(dayCnt + 2, 2) = "水"
                SetCellColor(dayCnt)
            Case 4
                Cells(dayCnt + 2, 2) = "木"
                SetCellColor(dayCnt)
            Case 5
                Cells(dayCnt + 2, 2) = "金"
                SetCellColor(dayCnt)
            Case 6
                Cells(dayCnt + 2, 2) = "土"
                SetCellholidayColor(dayCnt)
            Case Else
                Cells(dayCnt + 2, 2) = "日"
                SetCellholidayColor(dayCnt)
            End Select

            dayFlag = dayFlag + 1
            If (dayFlag > 7) Then
                REM 日曜まで到達したら、曜日フラグをリセット
                dayFlag = 1
            End If
        Else
            REM 月末日が31日より前の場合は、月末日の次の日から31日までの範囲を曜日も含めて空欄表示にする
            Cells(dayCnt + 2, 1) = ""
            Cells(dayCnt + 2, 2) = ""
        End If
    Next dayCnt

End Sub

Sub SetCellColor (cellPos As Integer)
    REM 平日なら曜日セルの背景をホワイトにする
    rangeVal = "B" + CStr(cellPos + 2)
    Range(rangeVal).Interior.Color = RGB(255, 255, 255)
End Sub

Sub SetCellholidayColor (cellPos As Integer)
    REM 土曜日か日曜日なら曜日セルの背景をライトグレーにする
    rangeVal = "B" + CStr(cellPos + 2)
    Range(rangeVal).Interior.Color = RGB(192, 192, 192)
End Sub

[参考にさせていただいたページ]
5 VBA To OOoBasic対比表(セル編) - 使ってみようOpenOffice.org
https://freeopenoffice.jimdofree.com/openoffice-org%E3%81%B8%E3%81%AE%E7%A7%BB%E8%A1%8C%E8%A7%A3%E8%AA%AC/5-vba-to-ooobasic%E5%AF%BE%E6%AF%94%E8%A1%A8-%E3%82%BB%E3%83%AB%E7%B7%A8/

プロシージャと関数 - Apache OpenOffice Wiki
https://wiki.openoffice.org/wiki/JA/Documentation/BASIC_Guide/Procedures_and_Functions

変換関数 (Apache OpenOffice 実行時ライブラリ) - Apache OpenOffice Wiki
https://wiki.openoffice.org/wiki/JA/Documentation/BASIC_Guide/Conversion_Functions_(Runtime_Library)

WEBカラー見本一覧(基本色、セーフカラー)
https://note.cman.jp/color/base_color.cgi

WILLsPage
別の職業から転身したシステムエンジニア兼プログラマ。 備忘録として使っていこうかと考え中。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした