Macro to Shade Cells with the color specified by 3 other cells?

  • Thread starter Thread starter Rob Miller
  • Start date Start date
R

Rob Miller

I have cells in a row with RGB values between 0 and 255.
I'd like another cell to be shaded in the color specified
by those three values.

Can this be done with a macro? If so, what is the macro?

Thanks.

-Rob
 
Rob,

You can use something like

Range("A1").Interior.Color = RGB( _
Range("B1").Value, Range("C1").Value, Range("D1").Value)

where B, C, and D have the Red, Green, and Blue values.

However, there's a problem here. Excel supports only 56 colors, although
those 56 colors can be any RGB color you want. These colors are stored in
the Workbook.Colors pallet array, and if you attempt to use a color that
isn't in this pallet, Excel will attempt to choose the closest match from
the pallet.

You can assign a color to the pallet with code like

ThisWorkbook.Colors(N) = RGB(red, green, blue)

where N a number between 1 and 56 indicating the which color element in the
pallet you want to change. Therefore, you can write your macro code like

ThisWorkbook.Colors(56) = RGB( _
Range("B1").Value, Range("C1").Value, Range("D1").Value)
Range("A1").Interior.ColorIndex = 56


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Back
Top