{"id":6382,"date":"2024-06-02T16:16:05","date_gmt":"2024-06-02T07:16:05","guid":{"rendered":"https:\/\/appbay.org\/?p=6382"},"modified":"2024-06-02T18:08:20","modified_gmt":"2024-06-02T09:08:20","slug":"vba-%e6%9d%a1%e4%bb%b6%e4%bb%98%e6%9b%b8%e5%bc%8f%e7%95%aa%e5%8f%b7%e6%af%94%e8%bc%83","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=6382","title":{"rendered":"VBA \u6761\u4ef6\u4ed8\u66f8\u5f0f(\u756a\u53f7\u6bd4\u8f03)"},"content":{"rendered":"

\u4ed6\u306e\u30b7\u30fc\u30c8\u306b\u7279\u5b9a\u306e\u5024\u304c\u3042\u3063\u305f\u5834\u5408\uff64\u4e21\u65b9\u306b\u8272\u3092\u4ed8\u3051\u308b<\/p>\n

Sub auto_open()\r\n\r\nCall CellsCondition\r\n\r\nEnd Sub\r\n\r\nSub CellsCondition()\r\n\r\nOn Error GoTo ex\r\n\r\nFor Each ws In ThisWorkbook.Sheets\r\n\r\n  If ws.Name <> \"xxx\" And ws.Name <> \"xxx\" Then\r\n  \r\n    ws.Columns(\"A\").FormatConditions.Delete\r\n    \r\n    For r = 5 To 125\r\n      For c = 1 To 1\r\n        Set f = ws.Cells(r, c).FormatConditions.Add(xlExpression, xlEqual, CreateFormatConditionsString(r))\r\n        f.Interior.Color = RGB(255, 0, 0)\r\n        f.StopIfTrue = False\r\n      Next c\r\n    Next r\r\n    \r\n  End If\r\n  \r\nNext\r\n\r\nExit Sub\r\n\r\nex:\r\n\r\nMsgBox \"\u6761\u4ef6\u4ed8\u66f8\u5f0f\u306e\u8a2d\u5b9a\u306b\u5931\u6557\u3057\u307e\u3057\u305f\u3002\"\r\n\r\nEnd Sub\r\n\r\nFunction CreateFormatConditionsString(r)\r\n\r\nFor Each ws In Sheets\r\n  If ws.Name <> \"xxx\" And ws.Name <> \"xxx\" Then\r\n    tmp = tmp + \"COUNTIF(\" & ws.Name & \"!C5:C125,C\" & r & \")+\"\r\n  End If\r\nNext\r\n\r\ntmp = Left(tmp, Len(tmp) - 1) ' \u6700\u5f8c\u306e+\u3092\u524a\u9664\r\nCreateFormatConditionsString = \"=AND((\" & tmp & \")>1,C\" & r & \"<>\"\"xxx\"\",C\" & r & \"<>\"\"xxx\"\",C\" & r & \"<>\"\"xxx\"\")\"\r\n\r\nEnd Function<\/pre>\n
 <\/p>\n
\u6761\u4ef6\u3060\u3051\u629c\u304d\u51fa\u3059\u3068\uff64
\n=AND((<\/span>
\nCOUNTIF(\u30b7\u30fc\u30c8\u540d!C5:C125,C20)+<\/span>
\nCOUNTIF(\u30b7\u30fc\u30c8\u540d!C5:C125,C20)<\/span>
\n)>1,C20<>”\u6bd4\u8f03\u9664\u5916”,C20<>”\u6bd4\u8f03\u9664\u5916”,C20<>”\u6bd4\u8f03\u9664\u5916”)<\/span>
\n\u3068\u306a\u3063\u3066\u3044\u308b\uff61<\/p>\n","protected":false},"excerpt":{"rendered":"
\u4ed6\u306e\u30b7\u30fc\u30c8\u306b\u7279\u5b9a\u306e\u5024\u304c\u3042\u3063\u305f\u5834\u5408\uff64\u4e21\u65b9\u306b\u8272\u3092\u4ed8\u3051\u308b Sub auto_open() Call CellsCondition End Sub Sub CellsCondition() On Error GoTo ex Fo … <\/p>\n
“VBA \u6761\u4ef6\u4ed8\u66f8\u5f0f(\u756a\u53f7\u6bd4\u8f03)” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[5],"class_list":["post-6382","post","type-post","status-publish","format-standard","hentry","category-1","tag-vba"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/6382","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6382"}],"version-history":[{"count":4,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/6382\/revisions"}],"predecessor-version":[{"id":6386,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/6382\/revisions\/6386"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6382"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6382"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6382"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}