カラーコードの調査テンプレート

カラーコードの調査テンプレート ポートフォリオ
カラーコードの調査テンプレート

カラーコード(#xxxxxx)を入力すると、色、RGB表示、反転色、補色、カラーネーム(ある場合のみ)を取得できるテンプレートです。

カラーコード調査テンプレートのダウンロード

カラーコード調査テンプレートの見本画像

カラーコード調査カラーコード調査

カラーコード調査テンプレートのサンプル動画

カラーコード調査テンプレートに使用した計算式

個別調査シートに使用した計算式

【C6】=HEX2DEC(MID($C$2,2,2))
【D6】=HEX2DEC(MID($C$2,4,2))
【E6】=HEX2DEC(MID($C$2,6,2))

【C7】=HEX2DEC(MID($C$4,2,2))
【D7】=HEX2DEC(MID($C$4,4,2))
【E7】=HEX2DEC(MID($C$4,6,2))

【C8】=255-$C$6
【D8】=255-$D$6
【E8】=255-$E$6

【C9】=SUM(MAX($C$6:$E$6),MIN($C$6:$E$6))-$C$6
【D9】=SUM(MAX($C$6:$E$6),MIN($C$6:$E$6))-$D$6
【E9】=SUM(MAX($C$6:$E$6),MIN($C$6:$E$6))-$E$6

【C10】=$C$6+256*$D$6+256*256*$E$6
【C11】=$C$7+256*$D$7+256*256*$E$7
【C12】=$C$8+256*$D$8+256*256*$E$8
【C13】=$C$9+256*$D$9+256*256*$E$9

【C16】=IF($C$2="","-",$C$2)
【C17】=IF($C$4="","-",$C$4)
【C18】=IF($C$2="","-",CONCATENATE("#",LOWER(DEC2HEX($C8, 2)),LOWER(DEC2HEX($D8, 2)),LOWER(DEC2HEX($E8, 2))))
【C19】=IF($C$2="","-",CONCATENATE("#",LOWER(DEC2HEX($C9, 2)),LOWER(DEC2HEX($D9, 2)),LOWER(DEC2HEX($E9, 2))))

【G16】=IF($C$2="","-",$C$2)
【G17】=IF($C$4="","-",$C$4)
【G18】=IF($C$2="","-",CONCATENATE("#",LOWER(DEC2HEX($C8, 2)),LOWER(DEC2HEX($D8, 2)),LOWER(DEC2HEX($E8, 2))))
【G19】=IF($C$2="","-",CONCATENATE("#",LOWER(DEC2HEX($C9, 2)),LOWER(DEC2HEX($D9, 2)),LOWER(DEC2HEX($E9, 2))))

【H16】=IF($C$2="","-",CONCATENATE("RGB(",$C6," ",$D6," ",$E6,")"))
【H17】=IF($C$4="","-",CONCATENATE("RGB(",$C7," ",$D7," ",$E7,")"))
【H18】=IF($C$2="","-",CONCATENATE("RGB(",$C8," ",$D8," ",$E8,")"))
【H19】=IF($C$2="","-",CONCATENATE("RGB(",$C9," ",$D9," ",$E9,")"))

【I16】=IFERROR(VLOOKUP($G16,データ!$A:$L,4,FALSE),"-")
【I17】=IFERROR(VLOOKUP($G17,データ!$A:$L,4,FALSE),"-")
【I18】=IFERROR(VLOOKUP($G18,データ!$A:$L,4,FALSE),"-")
【I19】=IFERROR(VLOOKUP($G19,データ!$A:$L,4,FALSE),"-")

一括調査シートに使用した計算式

【B6】=IF($A6="","",CONCATENATE("RGB(",HEX2DEC(MID($A6,2,2))," ",HEX2DEC(MID($A6,4,2))," ",HEX2DEC(MID($A6,6,2)),")"))
【C6】=IF($A6="","",HEX2DEC(MID($A6,2,2))+256*HEX2DEC(MID($A6,4,2))+256*256*HEX2DEC(MID($A6,6,2)))
【D6】=IFERROR(IF($A6="","",VLOOKUP($A6,データ!$A:$D,4,FALSE)),"-")
【E6】=IF($A6="","",CONCATENATE("#",LOWER(DEC2HEX(255-HEX2DEC(MID($A6,2,2)), 2)),LOWER(DEC2HEX(255-HEX2DEC(MID($A6,4,2)), 2)),LOWER(DEC2HEX(255-HEX2DEC(MID($A6,6,2)), 2))))
【F6】=IF($A6="","",CONCATENATE("RGB(",255-HEX2DEC(MID($A6,2,2))," ",255-HEX2DEC(MID($A6,4,2))," ",255-HEX2DEC(MID($A6,6,2)),")"))
【G6】=IF($A6="","",(255-HEX2DEC(MID($A6,2,2)))+256*(255-HEX2DEC(MID($A6,4,2)))+256*256*(255-HEX2DEC(MID($A6,6,2))))
【H6】=IFERROR(IF($A6="","",VLOOKUP($E6,データ!$A:$D,4,FALSE)),"-")
【I6】=IF($A6="","",CONCATENATE("#",LOWER(DEC2HEX(SUM(MAX(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))),MIN(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))))-HEX2DEC(MID($A6,2,2)), 2)),LOWER(DEC2HEX(SUM(MAX(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))),MIN(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))))-HEX2DEC(MID($A6,4,2)), 2)),LOWER(DEC2HEX(SUM(MAX(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))),MIN(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))))-HEX2DEC(MID($A6,6,2)), 2))))
【J6】=IF($A6="","",CONCATENATE("RGB(",SUM(MAX(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))),MIN(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))))-HEX2DEC(MID($A6,2,2))," ",SUM(MAX(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))),MIN(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))))-HEX2DEC(MID($A6,4,2))," ",SUM(MAX(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))),MIN(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))))-HEX2DEC(MID($A6,6,2)),")"))
【K6】=IF($A6="","",(SUM(MAX(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))),MIN(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))))-HEX2DEC(MID($A6,2,2)))+256*(SUM(MAX(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))),MIN(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))))-HEX2DEC(MID($A6,4,2)))+256*256*(SUM(MAX(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))),MIN(HEX2DEC(MID($A6,2,2)),HEX2DEC(MID($A6,4,2)),HEX2DEC(MID($A6,6,2))))-HEX2DEC(MID($A6,6,2))))
【L6】=IFERROR(IF($A6="","",VLOOKUP($I6,データ!$A:$D,4,FALSE)),"-")

6行目を7行目以降にコピーして使用

カラーコード調査テンプレートに使用したマクロ

個別調査シートに使用したマクロ

Sub カラー()
    a = Range(Cells(10, 3), Cells(10, 3)).Value
    b = Range(Cells(11, 3), Cells(11, 3)).Value
    c = Range(Cells(12, 3), Cells(12, 3)).Value
    d = Range(Cells(13, 3), Cells(13, 3)).Value
    
    Range(Cells(16, 3), Cells(19, 3)).Select
    With Selection.Font
        .Color = a
        .TintAndShade = 0
    End With
    
    Range(Cells(17, 3), Cells(17, 5)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = b
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range(Cells(18, 3), Cells(18, 5)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = c
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range(Cells(19, 3), Cells(19, 5)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = d
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("A1:A1").Select
End Sub

一括調査シートに使用したマクロ

Sub カラーb()
    上 = 1
    左 = 1
    下 = Range(Cells(上, 左), Cells(上, 左)).End(xlDown).Row
    右 = Range(Cells(上, 左), Cells(上, 左)).End(xlToRight).Column
    
    For i = 5 To 下 - 1
        
        Range(Cells(i + 1, 3), Cells(i + 1, 3)).Select
        a = ActiveCell.Value
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = a
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        If a > 8388608 Then
            With Selection.Font
                .Color = 0
                .TintAndShade = 0
            End With
        Else
            With Selection.Font
                .Color = 16777215
                .TintAndShade = 0
            End With
        End If
        
        Range(Cells(i + 1, 7), Cells(i + 1, 7)).Select
        a = ActiveCell.Value
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = a
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        If a > 8388608 Then
            With Selection.Font
                .Color = 0
                .TintAndShade = 0
            End With
        Else
            With Selection.Font
                .Color = 16777215
                .TintAndShade = 0
            End With
        End If
        
        Range(Cells(i + 1, 11), Cells(i + 1, 11)).Select
        a = ActiveCell.Value
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = a
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        If a > 8388608 Then
            With Selection.Font
                .Color = 0
                .TintAndShade = 0
            End With
        Else
            With Selection.Font
                .Color = 16777215
                .TintAndShade = 0
            End With
        End If
    Next
    Range("A1").Select
End Sub

コメント