根据单元格中的十六进制值设置自动彩色背景? [英] Set an automatic color background depending on the HEX value in the cell?

问题描述

我发现可以在将规则添加到单元格的条件格式规则"时手动完成:但是，我希望在单元格上写入十六进制值时，所有16,000,000个十六进制值颜色会自动显示，因此手动"将这些16,000,000个十六进制值相加听起来有点太多！是否没有办法让所有16,000,000种颜色的阴影自动找到并根据单元格中的HEX值将背景色应用于单元格?

换句话说，当我在单元格中输入0000ff时，我试图获得蓝色背景，然后我希望当单元格的HEX值更改时背景变为相应的颜色=>输入ff0000，然后输入00ff00为绿色，输入ffffff ...为白色，以此类推，以达到16,000,000及更高的颜色.

解决方案

A

I have figured it out it could be manually done while adding rules to the Conditional Format Rules of a cell: however, I want the all 16,000,000 HEX value colors to appear automatically when I write a HEX value on a cell, so "manually" adding these 16,000,000 HEX values sounds a bit too much! Isn't there a way so all 16,000,000 colors shades automatically find their way in and apply a background color to the cell depending on the HEX value in the cell?

In other word I am trying to get a blue background when I type 0000ff in the cell, then I d like the background to change to the corresponding color when the HEX value of the cell is changed => BGC changes to red when ff0000 is entered, then green when 00ff00, white when ffffff ...etc for the 16,000,000 and up colors possible.

解决方案

A Worksheet_Change event macro that converts the hexadecimal to an RGB should do this handily. The worksheet's HEX2DEC function should be able to handle the conversion.

Right click the worksheet's name tab and choose View Code. When the VBE opens, paste the following into the code sheet titled something like Book1 - Sheet1 (Code).

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Dim rng As Range, clr As String
    For Each rng In Target
        If Len(rng.Value2) = 6 Then
            clr = rng.Value2
            rng.Interior.Color = _
              RGB(Application.Hex2Dec(Left(clr, 2)), _
                  Application.Hex2Dec(Mid(clr, 3, 2)), _
                  Application.Hex2Dec(Right(clr, 2)))
        End If
    Next rng

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Tap Alt+Q to return to your worksheet. Type any 6 character hexadecimal code into a cell to provide a background color.

        

这篇关于根据单元格中的十六进制值设置自动彩色背景?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！