Color cells bsed on table of RGB numbers

nicksoph

Board Regular
Joined
Jun 13, 2009
Messages
57
Hi
Im using Excel 2007 and have a table of 3 colums: Each row gives a colours RGB values in numbers from 1 to 255. I would like the 4th row to display the color based upon the numbers in the first cells.

Any help would be appreciated.

Best wishes
Nic
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Bill Rockenbach

Well-known Member
Joined
May 4, 2009
Messages
1,415
Place this code in the sheet code module. It will change the color in column D as RBG color numbers are intered into columns A,B and c.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Long
If Not Intersect(Target, Range("A:C")) Is Nothing Then
row = Target.row
If Target < 0 Or Target > 255 Then
MsgBox "An invalid numbr as been intered to cell " & Target.Address
Application.Undo
End If
Cells(row, 4).Interior.Color = RGB(Cells(row, 1), Cells(row, 2), Cells(row, 3))
End If
End Sub

If you want to create the colors for all rows in a loop then use tis code in the worksheet code module
 
Upvote 0
L

Legacy 68668

Guest
Bill

Using "row" as a variable name is not recommended since Row is a reaserved word for VBA.

And you will need to dim as Row, so that the row property becomes Row.

row = Target.row
 
Upvote 0

Bill Rockenbach

Well-known Member
Joined
May 4, 2009
Messages
1,415
Corrected code
Since excel can only use colors that are on the color palette, when you change a cell by RGB excel chooses the closest match on the color pallet. To achieve the exact RGB color the color palette has to be change. This will produce the exact RGB color for the selected cell, but all other cells using the color pallet item whose color was changed will reflect that color change.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long
    If Not Intersect(Target, Range("A:C")) Is Nothing Then
        lRow = Target.Row
        If Target < 0 Or Target > 255 Then
            MsgBox "An invalid number has been entered to cell " & Target.Address
            Application.Undo
        End If
        ActiveWorkbook.Colors(2) = RGB(Cells(lRow, 1), Cells(lRow, 2), Cells(lRow, 3))
        Cells(lRow, 4).Interior.ColorIndex = 2
        'ActiveWorkbook.Colors(2) = RGB(255, 255, 255)
    End If
End Sub


Seiya,
I aggree, just get sloppy sometimes. Not too professional.
 
Last edited:
Upvote 0

nicksoph

Board Regular
Joined
Jun 13, 2009
Messages
57
Thanks for the replies, Im stunned by how quickly forum questions are answered.

I didnt express my level of ignorance. "Option Explicit" - User Clueless

I have tried to find soloutions to my ignorance on the and so far have;

saved the workbook as an xlsm and enabled macros

Right clicked the sheet tab selected "View code" and pasted Bill's 2nd code sample into the VB editor - saved this and then gone back to my sheet.

Now when I change the values in colums a b or c the colours in coloum d all change to one colour rather than just the row changed.

looking at the code in the Vb editor I see
'ActiveWorkbook.Colors(2) = RGB(255, 255, 255) - is highlighted in green

Any help appreciated,

Best wishes
Nic
 
Upvote 0

Bill Rockenbach

Well-known Member
Joined
May 4, 2009
Messages
1,415
Option Explicit means that you have to Dimension your variables or you will get a run time error for those variables not dimensioned. Recommend always doing this to prevent spelling variables inconsistently causing unpredictable results.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
This will produce the exact RGB color for the selected cell, but all other cells using the color pallet item whose color was changed will reflect that color change
<o:p></o:p>

<o:p></o:p>
Read the fine print.<o:p></o:p>
<o:p></o:p>
Either you get a color off the palette that is close to the RGB specs in your columns as in my first code posted when the cell is directly assigned the RBG numbers or you change the color pallet color and all cells assigned that color will change. <o:p></o:p>
<o:p></o:p>
You could program a different color on the color palette for each row, but you are limited to the number of color cells in the color pallet. <o:p></o:p>
How many rows do you have??
 
Upvote 0

nicksoph

Board Regular
Joined
Jun 13, 2009
Messages
57
Thanks Bill

It may be that Im not going to be able to what I want using Excel.

I have a spreadsheet that gives the x,y co-ordinates and radius for about about 10,000 circles. This spreadsheet is used to generate a text file which is used by CorelDraw to plot those circles. The spreadsheet can also pass on the colours for those circles. What I wanted to do was edit the numbers in Excel and be able to manipulte the colours and see the results instantly and without using a colour picker which is impractical for that many circles.

Thank you for your time.
best wishes
nic
 
Upvote 0

nicksoph

Board Regular
Joined
Jun 13, 2009
Messages
57
To answer your question - I would wish to have a pallet of all RGB colours but would need one of at least 1000 colours
Nic
 
Upvote 0

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Assuming that your table of RGB numbers is in columns A to C then the following will color column D accordingly.

First click the officer button
officebuttonxj7.png
then click Excel Options, check Show Developer tab in the Ribbon and click OK.

Press ALT + F11 to open the Visual Basic Editor, then select Module from the Insert menu. Paste the following into the white space on the right:

Code:
Sub ColorMe()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("D" & i)
        .Interior.Color = RGB(.Offset(, -3).Value, .Offset(, -2).Value, .Offset(, -1).Value)
    End With
Next i
End Sub
Press ALT + Q to return to your sheet. On the Developer tab click Macros, click on ColorMe and click the Run button.
 
Upvote 0

Forum statistics

Threads
1,187,004
Messages
5,961,073
Members
438,516
Latest member
Fintrics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top