CODEWITHSUNDEEP
excelexcel-formula
Joe07041976
Joe07041976

Reputation: 11

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)

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

Upvotes: 0

Views: 477

Answers (3)

Joe07041976
Joe07041976

Reputation: 11

The actual answer is:

For x = StartRowNumber To EndRowNumber
    Cells(x, 4).Interior.Color = RGB(Cells(x, 1), Cells(x, 2), Cells(x, 3))
Next

Upvotes: 1

bosco_yip
bosco_yip

Reputation: 3802

Using Conditional Formatting way

Rule of cell color appear in Column A, Column B and Column C:

  • Red color : Column A only
  • Green color : Column B only, Column A + Column B
  • Blue Color : Column C only, Column A + Column C, Column B + Column C, Column A + Column B + Column C

Then,

In Column D, select D1:D7 >> Conditional formatting >> New rule >>

  1. Rule 1 formula: =LOOKUP(2,1/($A1:$C1<>""),ROW($A$1:$A$3))=1 >> Format >> "Red" background Color >> OK
  2. Rule 2 formula: =LOOKUP(2,1/($A1:$C1<>""),ROW($A$1:$A$3))=2 >> Format >> "Green" background Color >> OK

  3. Rule 3 formula: =LOOKUP(2,1/($A1:$C1<>""),ROW($A$1:$A$3))=3 >> Format >> "Blue" background Color >> OK

  4. Finish

enter image description here

Column D colored cell result :

enter image description here

Upvotes: 0

Variatus
Variatus

Reputation: 14383

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.

Upvotes: 1

Related Questions