I have an Excel sheet that has 3 columns A B C columns and I want the D column to be the color of RGB(A,B,C)

Question

I am using Excel 2019/365 32 bit.

I have a sheet with many rows and 3 columns Column A is the Red component (an integer from 0 - 255) Column B is the Green component (an integer from 0 - 255) Column C is the Blue component (an integer from 0 - 255) Of the color formula RGB(A,B,C) I want column D to be the color that would result from RGB(A,B,C) for that row How do I assign a color to column D?

The RBG(A,B,C) A, B, C can be any integer from 0 to 255 for example RGB(255,0,0) = the color Red, Yellow is RGB(255.255,0), Green = RGB(0,255,0), Blue = RGB(0,0,255)

Conditional Formatting is not possible since the possible combinations of colors is over 500 possible colors. So I need a function or VBA Code to fill D with the color that responds to the RGB().

An example of what I want where the colors you see are calculated not conditional formatting

Variatus Variatus · Accepted Answer · 2020-04-18T02:10:03

Unfortunately, you don't disclose the logical purpose for the code you ask. Therefore I have invented one. If it doesn't suit your needs you may be able to tweak the code's functionality in the direction of your requirements. First, try it out.

Install the code below in the code module of a blank worksheet. It's important that the code be in that particular module. It's identified in the VB Editor's Project Explorer by its name.

Private Sub Worksheet_Change(ByVal Target As Range)

    ' identify the columns containing R, B & G
    Const TriggerAddress As String = "A:C"

    Dim Rng As Range
    Dim Arr As Variant

    If Not Application.Intersect(Target, Range(TriggerAddress)) Is Nothing Then
        With Target
            ' ignore changes by Paste action
            If .Cells.CountLarge = 1 Then
                Set Rng = Range(TriggerAddress).Rows(.Row)
                ' require 3 numbers
                If Application.Count(Rng) = 3 Then
                    Arr = Rng.Value
                    Cells(.Row, Rng.Cells.Count + 1).Interior.Color = _
                    RGB(Arr(1, 1), Arr(1, 2), Arr(1, 3))
                End If
            End If
        End With
    End If
End Sub

The constant at the top of the procedure identifies the columns where you have the numbers. You asked for A:C and that's what it is now but it can be changed in the future. Note that the output will be in the horizontally adjacent cell, as you requested, but that can be changed too.

The code will react when any cell in the TriggerColumns is changed. If all 3 cells contain numbers the fill color of the output cell will be changed. Not that 0 (zero) is a "number" in this context. Modify any of 3 existing numbers and the color is reset again.

I have an Excel sheet that has 3 columns A B C columns and I want the D column to be the color of RGB(A,B,C)

3 Answers