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

Question

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

Answer 1

You will need 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 need to use the function Worksheet_change of all spreadsheets 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 predefined colors in a table, you can do this conditional formatting (with defined color limitations). Something like:

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

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

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

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

Here has an advanced formatting model with data table as set out.

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

Observing:

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. Since conditional formatting requires that the color be manually specified in a rule, the number of rules will also be huge (also, 256³ rules);

That is, it is simpler and easier to do in VBA.

Issue 2

I tested the formula VBA and updated to work properly, remembering that one should do some tests with the Target text. because not containing the desired format will result in error.

I hope I’ve helped!