Can conditional formatting use RGB values specified in a set of cells?

Question

Let's say that I have three cells with RGB codes (one each for R, G, and B values). Is it possible to have a conditional format set up so that it will use the RGB values in those cells?

If, for example, I have the three cells as 255,0,0 (bright red), then cells that use that conditional format would show in bright red. If I decide to change the cells to bright blue (0,0,255), the conditional format would immediately recognize that change and apply it. So whenever I decide, for whatever reason, to use a different color, all I need do is change the RGB cell values and presto. In this way, a single conditional format would suffice, rather than a separate rule for each color I may want to use.

Answer 1

Hi Michael,

Yes, conditional formatting is not the solution for your requirement. My best guess is VBA. I suppose with VBA you can achieve what you wanted.

If you need help with VBA/macro**,** please see Office VBA support and feedback for guidance about the ways you can receive support for VBA programming questions and provide feedback.****

The forum mentioned in the above support article is the right channel to get the support for questions related to programming. We have limited resources and very little knowledge about VBA/macros and in that forum, you will get the most qualified pool of respondents, and other partners who read the forum regularly can either share their knowledge or learn from your interaction.

We highly appreciate your understanding.

Regards,

Neha

Answer 2

There is an entertaining video on youtube called 'Stand-up comedy about spreadsheets', which actually accomplishes this.

There is also a website where you can convert an image with the aid of conditional formatting. Maybe that could be a starting point?

EDIT: I looked it up and you could do conditional formatting based on a graded color scale. Going from one color to another (black to absolute red with fixed values 0 to 255 eg, or black to white for grayscale use). Depending on the use case, this gives you at least something, even if it's not exactly RGB.

Answer 3

https://www.youtube.com/watch?v=fhE9bGohbIM

This is a video showing some VBA coding to take the color from cells in the spreadsheet and apply it to columns.

Here is my cut down VBA code and a little corrected from the video: (you don't multiply green by 255, it would be 256, but with the RGB function you don't do that at all)

Sub macroRGB1()

    For Each cell In Selection

        R = cell.Value

        G = cell.Offset(0, 1).Value

        B = cell.Offset(0, 2).Value

        Cells(cell.Row, 1).Resize(1, 3).Interior.Color = RGB(R, G, B)

    Next cell

End Sub

Then you make some cells like:

               A                  B                   C

 1             255              125               0

 2            125               125              125

 3            0                   64                 255

Select the 3 cells in A1:A3, and then run your macro.

Change the numbers in the Red, Green or Blue columns respectively, select the range, and run the macro again.

Here is a pretty picture of it in operation

I suppose it may be possible to apply this to a conditional formatting background color format via VBA as well.  

To find out how to do this in VBA, you can record a macro - set up a basic conditional format, then end the recording. 

Edit your newly created macro to grab the colors from a specific or highlighted location in the spreadsheet like this macroRGB1() does.

Your new macro for conditional formatting could first clear all the conditional formatting, then apply some new conditional formatting according to your rules, and apply the color as read from the spreadsheet cells.

Alternatively, and more easily, you could edit the existing conditional format to simply change the background color.

If you wanted to get fancy; you could read the background color of a cell in the spreadsheet, then use that read color as your applied color - no calculation required.  So you can visually pick a color used for the conditional format.  Like this:

Sub macroRGB2()

    For Each cell In Selection

        colorRGB = cell.Interior.Color

        Cells(cell.Row, 1).Resize(1, 3).Interior.Color = colorRGB

    Next cell

End Sub

And if you wanted to get really fancy, you could research how to automatically run the macro when the data in your cell changes!  That way you just update the numbers, and voila your conditional format is updated!