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

 

[Dan_W]

I did have a little trouble with the Hex code when coloring the labels in the userform because of the characters being in a different order, but once Google helped me figure that out, it all came together.

As you've discovered, Excel understands hex as BGR rather than RGB. If it's helpful at all, the following will convert RGB to Hex and Hex to RGB (as individual Red, Green and Blues colours) and from the native VBA color code to the separate RGB values (in case you ever need it).

Sub VBA2RGB(ByVal VBAColor As String, ByRef RedColor As Long, ByRef GreenColor As Long, ByRef BlueColor As Long)

    RedColor = (VBALong Mod 256)
    GreenColor = (VBALong \ 256) Mod 256
    BlueColor = (VBALong \ 65536) Mod 256
   
End Sub

Sub Hex2RGB(ByVal HexCode As String, ByRef RedColor As Long, ByRef GreenColor As Long, ByRef BlueColor As Long)

    HexCode = VBA.Replace(HexCode, "#", "")
    HexCode = VBA.Right$("000000" & HexCode, 6)
    BlueColor = VBA.val("&H" & VBA.Mid(HexCode, 5, 2))
    GreenColor = VBA.val("&H" & VBA.Mid(HexCode, 3, 2))
    RedColor = VBA.val("&H" & VBA.Mid(HexCode, 1, 2))
End Sub

Function RGB2Hex(ByVal RedColor As Long, ByVal GreenColor As Long, ByVal BlueColor As Long) As Long
    RGB2Hex = "#" & VBA.Right$("00" & VBA.Hex(RedColor), 2) & VBA.Right$("00" & VBA.Hex(GreenColor), 2) & VBA.Right$("00" & VBA.Hex(BlueColor), 2)
End Function

(Apologies for any typos - I did this on my phone)

 

[zookeepertx]

Honestly... I still haven't recovered from the whole 'Pluto is not a planet's fiasco... and now this..?!

On a slightly related noted, did you see the post in the Articles section re: the Javascript MixBox.js colour library? (Link) it looks amazing. I'm determined to get ChatGPT to convert it into VBA for me (which as little hand holding or corrections by me as possible)

Yeah - who's in charge of deciding what's a planet anyway!? Did they get voted in or did they just randomly decide THEY could make the decision? And is it based on volume of the object in question or what? (I once spent quite a lot of time online reading about the difference between streams, creeks, rivers, etc. and between ponds, lakes, seas, oceans and whatever other water features exist. And don't even let me get started on different types and variations of clouds! It's possible that I may be too easily amused for my own good, )

I have not seen that post. In fact, I've never been to the Articles section. Truth be told, I didn't know there was one!

Did you see that I actually FINALLY figured out how to create a Userform showing the colors that I most often need and to have it stay open? I was thrilled and really proud. Of course, I wouldn't have gotten there without the input from you and from jolivanes. Both got me to thinking outside the "box" that only existed in my head, LOL! So, I want to mark this thread as Solved and mark a post as the solution, but don't know which one to mark. I don't guess I can mark like 3 of them, since those posts collaborated with my brain to come up with the solution.

And my current problem is that I created the Userform in a "Dummy" workbook and that's the only place it is. How do I get that Userform into my Personal without having to copy ALL of that code again? The Module to call up the Userform was easy to recreate in Personal because it's only 3 lines long. Userform is much, MUCH longer.

Hope you had a good weekend

Jenny

 

[zookeepertx]

I got annoyed at trying to move the Userform over and realized it could be simple! I Exported it to my desktop and then imported it to my Personal. So, I think I'm done with that! At least until the next weird problem comes up, LOL!
Although I am curious as to why, when I exported it to my desktop, TWO icons appeared instead of just one. Hmmmm

Actually, I often wonder why, when I click on the link to see the macros available, the box containing them doesn't allow me to scroll down the list. I have to grab the handle ("scroller" or whatever it's called) to see the whole list.

 

[Dan_W]

And my current problem is that I created the Userform in a "Dummy" workbook and that's the only place it is. How do I get that Userform into my Personal without having to copy ALL of that code again? The Module to call up the Userform was easy to recreate in Personal because it's only 3 lines long. Userform is much, MUCH longer.

You can export the form, and then import the form in your PERSONAL.XLSB file. When it exports userforms, VBA creates two files (extensions .FRM and FRX) - make sure that they are both in the same directory when you import the the FRM file, otherwise you'll get an error.

 

[Dan_W]

I got annoyed at trying to move the Userform over and realized it could be simple! I Exported it to my desktop and then imported it to my Personal. So, I think I'm done with that! At least until the next weird problem comes up, LOL!
Although I am curious as to why, when I exported it to my desktop, TWO icons appeared instead of just one. Hmmmm

Actually, I often wonder why, when I click on the link to see the macros available, the box containing them doesn't allow me to scroll down the list. I have to grab the handle ("scroller" or whatever it's called) to see the whole list.

Oops - sorry - didn't see this one. I see you worked it out - and my point re: the two files is addressed in my response above.
When you say you click the link to see the macros available, which link are you referring to? The Macros button next to the Visual Basic button on the Developer tab?