excel – How to format a cell from the RGB code contained in the cell itself

Question:

Is it possible to change the background color of a cell from an RGB or hexadecimal code written as text in the cell itself? Let's suppose I write 255,0,0 in célula A1 and when I press enter the cell's background changes to red!

Answer:

You will have to use VBA for this, test the code below for cell A1:

Range("A1").Interior.Color = RGB(127,187,199)

If you want to do this for all cells, you will have to use the Worksheet_Change function for all sheets something like:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim clrCor As Long
Dim arrCores() As String

    arrCores = Split(Target.Text, ",")
    clrCor = RGB(CInt(arrCores(0)), CInt(arrCores(1)), CInt(arrCores(2)))

    Target.Interior.Color = clrCor

End Sub

Issue 1

Responding to the comment below…

If you have the predefined colors in a table, you'll be able to do this conditional formatting (with defined color limitations). Something like that:

| Tabela de Cores |
| Azul            |
| Vermelho        |
| Verde           |

In this table you will create a predefined list (eg lstCor).

In the table you want to conditionally format, you will place cell validation with list. So that the choice is always within the options already defined.

In its conditional formatting, it will link the value of the active cell, and according to the result, Blue will format it with Blue, in this case you will have to have a formatting rule for each registered color.

Here is an advanced formatting template with data table, as shown.

If you have questions about how to list and validate, click here .

Note :

This same idea of ​​using tables could be extended by using a table with all RGB colors. Only:

  1. The combination of values ​​in the table will be huge (256³ items in the list);
  2. As conditional formatting requires the color to be manually specified in a rule, the number of rules will also be huge (equally 256³ rules);

In other words, it's simpler and easier to do in VBA.

Edition 2

I tested the VBA formula and updated it to work correctly, remembering that you should do some tests with Target.text because if it doesn't contain the desired format it will result in an error.

Hope this helps!

Post Views: 1
Scroll to Top