Excel color palette

[zookeepertx]

Hello all,
It used to be that you could right-click to show the Cell Color palette to change a cell's color and then grab the palette and drag the whole box down so it showed all the time. You could do the same thing with the Font Color palette. Now, you have to right-click and select directly from the displayed palette instead of having the palette already there to just choose from.
I have several worksheets that I have to go through and evaluate a large number of cells, one at a time, then decide what color to make the cell. Because there are so many cells to evaluate, it's kind of a (small) pain to have to ask for the palette every single time I need to change a cell's color. Is there a way to get that little box with the color palette to show all the time like you used to be able to do?
Conditional Formatting won't work because there is such a variance of reasons why a cell needs to be a particular color.

This little box is what I'm talking about.

(I hope this question is clear. I tend to "over-describe" things and confuse people when I'm typing/writing)

Thanks!

Jenny

 

[zookeepertx]

Right? If nothing else, making them look good makes it easier to me to get what I need from them! I'm also a ****** for color coding things, so here we are. LOL! Plus, often when I'm "prettying" them up, I find a way to make them work better, too.

LOL! So, apparently you can't use the word "s u c k e r" on the board!

 

[zookeepertx]

Hi Dan.
This problem is not the only one Microsoft has created from what I gather reading some of the posts in excel forums.

I quickly did a test and it looks like this might do what you want.

In a regular module

Sub Show_Me()
    Color_Palette.Show vbModeless
End Sub

Insert a UserForm and in it's Properties Window change it's name from "UserForm1" to "Color_Palette"
Add as many Labels as required for all the different colors you want.
In the Properties Window for each label at the "Backcolor" line, select "palette" and click on the color that you want that Label to have.
Do this for all your Labels.
For each Label, use this code, changing the "Label1_Click" to "Label2_Click", "Label3_Click" and so on.

Private Sub Label1_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label1").BackColor    '<---- Change Label number as required
End Sub

When back in your sheet, call the "Color_Palette", select your range, single cell or multiple cells, to be colored and click on the label that has the desired color.

If I knew how to get the name of the clicked Label, code could be made less cumbersome.

View attachment 83646

Thanks for the reply. But would that stay open or would I still need to bring this up each time I want to color another cell?

 

[jolivanes]

vbModeless will keep it open until you close it.
I made one to try it with only 10 colors and it works like a charm.
As a matter of fact, I added it to the QAT Userform

 

[zookeepertx]

Which is essentially what my first suggestion was: Excel color palette

It is, afterall, just a modeless userform with labels the user clicks to select a colour. From my perspective, it's all much of a muchness because I've coded all the above solutions already, it's just a question of adapting to someone else's needs, I think, and making sure that the code isn't woefully embarrassing

Of course, the benefit of using a userform is that you could move the form around as required, rather than having to move the cursor back and forth between the ribbon and the worksheet... .my primary concern thus far is just making sure that VBA is a workable solution, and isn't blocked by the work computers.

The best solution would be if MS could reverse course on this decision of theirs to make the palette modal... because...why did they have to do that?!?! Sigh

Hi Dan,

Sorry for not getting back sooner; I had to get a bunch of month-end reporting done and TPTB get upset if I don't prioritize that.

I didn't realize that your first suggestion was a userform. So "modeless" means that it would stay open till it's intentionally closed? That sounds like it would be virtually the same as the tear off palette, and might be easier to create! (Not for ME... for YOU. If we waited for ME to create it - no matter how easy - we'd both be old and grey and I still wouldn't have been successful, LOL!)

Below I'll put a chart showing the 26 colors that I use the most along with their RGB values. (Why do White and No Fill have the same RGB? No Fill does appear to be white, but the gridlines are still visible, while selecting White makes the gridlines disappear, so shouldn't there be SOMETHING different between them?)

On rare occasions, I might need to use some different colors as well. With the modeless form visible would I still be able to open the color palette for a minute to select those?

Thanks for being patient with me!

Jenny

 

[jolivanes]

When do you use #1 and when #21 of your colors?

 

[jolivanes]

And when and #15 and #18

 

[jolivanes]

If you use labels
For the white color (label backcolor is white) you use this

ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label23").BackColor

or this

ActiveSheet.Range(Selection.Address).Interior.Color = RGB(255, 255, 255)

and for blank you use

ActiveSheet.Range(Selection.Address).Interior.Pattern = xlNone

As you noticed, there is a difference. One is white and the other has no color.

 

[zookeepertx]

And when and #15 and #18

All of the colors given are used in a couple of different workbooks. Some colors are used in more than one workbook.

Ooops! On 15 and 18: I use each of those on different reports and while I was collecting all the colors I use at various times I didn't realize those were the same! I've gotten rid of (what used to be) #18.
Re: 1 and 21 - the RGB looks different for each one. Is there a problem with them that I'm missing?

Thanks!

Jenny

 

[jolivanes]

Re: "All of the colors given are used in a couple of different workbooks. Some colors are used in more than one workbook."
I can't see what that has to do with the color palette but maybe I am missing something.

Re: 1 and 21
The top color and the 4th from the bottom have the same RGB's
No problem at all, I just noticed it when trying out the color palette.

 

[zookeepertx]

Re: 1 and 21
The top color and the 4th from the bottom have the same RGB's
No problem at all, I just noticed it when trying out the color palette.

Dangit they were the same! I forgot that I found that last evening & deleted the later one but forgot to post it here. Yesterday I was able to get the Hex values on each color. It was SO simple! Duhhhh... I should have known that! In comparison with the RGB values, the Hex values make absolutely NO sense at all! I get that the first 2 characters represent Red, middle 2 characters represent Green and the last 2 represent Blue. But I can't see any relation between the "amount" of - for example, the fifth color down (the bright yellow) - 255R vs 255G vs 0B. Okay - 0 Blue makes sense, but how can the rest be equally R and G?! It's YELLOW!
And then Bright Red vs pink - I totally understand Red being 255,0,0; makes complete sense. But Pink is 255,204,255. There's no Green or Blue in Pink!
(Someone has to wonder about this kind of stuff and I'm really good at it, LOL!)
And, on the subject of color mixing... What is Green doing in there anyway? The color wheel is based on Red, YELLOW and Blue. Green is between yellow and blue and is not a primary color. You have to mix those 3 - R, Y and B - to get whatever other colors you need, although white needs to be available to turn red into pink.
Okay, I'll stop now. Things like this just bother me. (This is where the OCD in my signature line might just come in. )