Interior.Color RGB Values

DocAElstein · Jul 30, 2014

Interior.Color RGB Values

Hello
… I am almost there on this one and need a last bit of help on my third code……

. I have written 3 codes to return the Interior.Color RGB Values of a cell. They all work, that is to say they give me the R G B integer values for the Interior Color of any cell (In my examples here it is Cell (1, 1) of the Active Sheet).
. The first code I wrote completely myself and is stupid because it takes ages!!!( The Program simply goes through every single color format number given by property RGB(R,G,B) and compares it with that single color format number given by the .Color Property applied to cell (1,1) until a match is found.)

Option Explicit ' ' Code 1 Sub FindInteriorColorRGBColourIndexLongWay1() On Error GoTo TheEnd 'Stop program (and reset Status Bar) if anything goes wrong ' This Program simply goes through every color number given by RGB(R.G,B) and compares it with that given by the .Color Property until a match is found. Dim R As Byte, G As Byte, B As Byte ' R G B are from 0 to 255 so a Byte size variable is sufficient For R = 0 To 255 Step 1 ' For every R value go..... For G = 0 To 255 Step 1 ' ...for every G value go.... For B = 1 To 255 Step 1 '....through every B value.... Application.StatusBar = R & " " & G & " " & B ' give current R G B values in Left Under Window corner If ActiveSheet.Cells(1, 1).Interior.Color = RGB(R, G, B) Then '....until a match is found MsgBox ("R" & vbTab & "G" & vbTab & "B is " & R & vbTab & G & vbTab & B) GoTo TheEnd Else End If If B = 255 Then Exit For ' Extra line needed to prevent Byte being given value > 255 Next B If G = 255 Then Exit For Next G If R = 255 Then Exit For Next R TheEnd: Application.StatusBar = False End Sub 'FindInteriorColorRGBColourIndexLongWay1() '______________________________________________________________________________________- '_______________________________________________________________________________ '__________________________________________________________________________________ '______________________________________________________________

. The second and third codes I found by a bit of “Googeling”, trial and error, by racking my brain, and playing around in Excel with Tables such as :

Book1

A

B

C

D

E

F

G

1

2

&H10000

&H1000

&H100

&H10

&H1

3

B

G

R

4

HexStuff

65536

4096

256

16

1

.Color = (F$4*F5)+(E$4*E5)+(D$4*D5)+(C$4*C5)+(B$4*B5)

5

R

1

6

G

2

512

7

B

1

65536

8

RGB

2

131586

Tabelle1

Cell Formulas
Range	Formula
G5	=(F$4F5)+(E$4E5)+(D$4D5)+(C$4C5)+(B$4*B5)
G6	=(F$4F6)+(E$4E6)+(D$4D6)+(C$4C6)+(B$4*B6)
G7	=(F$4F7)+(E$4E7)+(D$4D7)+(C$4C7)+(B$4*B7)
G8	=(F$4F8)+(E$4E8)+(D$4D8)+(C$4C8)+(B$4*B8)

.. I managed to understand the second code and have written appropriate comments:-

'__________________________________________________________________________________ '______________________________________________________________ ' Code 2 Sub FindInteriorColorRGBColourIndexUsingHexStuff2() Dim R As Byte, G As Byte, B As Byte, RGBColour As Long RGBColour = ActiveSheet.Cells(1, 1).Interior.Color R = RGBColour Mod 256 ' Mod gives what is left over from RGBColour after dividing by 256 : All .color numbers are = (multiples of 256 for green and Blue: (256,512....(255 x 256)) for green ; (65536,131072.....(255 x 65536) for Blue)) + (0 to 255 depending on the Rot value)). So here the Mod blocks out Green and Blue multiples, and leaves just the 0 - 255 step 1 integer which represents Red G = (RGBColour Mod 256 ^ 2) \ 256 ' Mod gives what is over after dividing by 256^2=65536. This blocks out numbers which include multiples of 65536 which are Blue numbers,(As Blue numbers are multiples of 65536). \ is divide, but only return integer part. This gives numbers which are the exact multiples of 256(which are Green numbers). This effectively blocks out the Red part of the number (0-255). B = RGBColour \ (256 ^ 2) '\ is divide, but only return integer part. This gives numbers which are the exact multiples of 65536(which are Blue numbers). This effectively blocks out the Red part of the number (0-255 step 1), and the green part (256-65280 step 256) MsgBox ("R" & vbTab & "G" & vbTab & "B" & vbTab & "RGBColour is " & R & vbTab & G & vbTab & B & vbTab & RGBColour) End Sub 'FindInteriorColorRGBColourIndexUsingHexStuff2() '_______________________________________________________________________________________- '_______________________________________________________________________________ '__________________________________________________________________________________ '______________________________________________________________

… I just need help to understand my third code below……

'__________________________________________________________________________________ '______________________________________________________________ ' Code 3 Sub FindInteriorColorRGBColourIndexUsingHexStuff3() Dim R As Long, G As Long, B As Long, RGBColour As Long RGBColour = ActiveSheet.Cells(1, 1).Interior.Color R = RGBColour And vbRed 'vbRed = 255 .....??? ... wot is happening with AND here??? G = (RGBColour And vbGreen) ' ....wot is happening with AND here??? ??? G = (RGBColour And vbGreen) \ &H100 'vbGreen = 255 x 256 = 65280 ; &H100 = (256 x 1) + (16 x 0) + (1 x 0) = 256 B = (RGBColour And vbBlue) ' ....wot is happening with AND here??? ??? B = (RGBColour And vbBlue) \ &H10000 'vbBlue = (256^2) x 255 = 16711680 ; &H10000 = (65536 x 1) + (4096 x 1) + (256 x 0) + (16 x 0) + (1 x 0) = 65536 MsgBox ("R" & vbTab & "G" & vbTab & "B" & vbTab & "RGBColour is " & R & vbTab & G & vbTab & B & vbTab & RGBColour) End Sub 'FindInteriorColorRGBColourIndexUsingHexStuff3() '_______________________________________________________________________________ '__________________________________________________________________________________ '______________________________________________________________

.. I am a beginner and am keen to understand exactly what is going on.. I am stuck on understanding this third code. Probably I do not fully understand what the And is doing in this code . I have used And lots in conditional stuff, but I just do not see what it is doing in the third Code.
Can anyone explain this to me and / or suggest any alternative codes?

Thanks
Alan
Bavaria

P.s. Full Excel files in XL2003 and XL 2007 with macros in module “FindCellInteriorRGBValues” are here:
FileSnack | Easy file sharing
FileSnack | Easy file sharing

NeonRedSharpie · Jul 30, 2014

Here's a workbook function to get hex:

Code:

Function HexCode(Cell As Range) As String    
HexCode = Right("000000" & Hex(Cell.Interior.Color), 6)
End Function

This returns the hex code in RGB order

Code:

Function getRGB1(rcell) As String
    Dim sColor As String


    sColor = Right("000000" & Hex(rcell.Interior.Color), 6)
    getRGB1 = Right(sColor, 2) & Mid(sColor, 3, 2) & Left(sColor, 2)
End Function

This will return the RGB value in the cell using a worksheet function:

Code:

Function getRGB2(rcell) As String
    Dim C As Long
    Dim R As Long
    Dim G As Long
    Dim B As Long


    C = rcell.Interior.Color
    R = C Mod 256
    G = C \ 256 Mod 256
    B = C \ 65536 Mod 256
    getRGB2 = "R=" & R & ", G=" & G & ", B=" & B
End Function

DocAElstein · Jul 30, 2014

Hi NeonRedSharpie
. Thanks for the extra Functions. I got my teeth into them, got them to work, and understand them. I re wrote them with comments to help me understand / remember what is going on.

 Option Explicit 'Here's a workbook function to get hex(In B G R order which is the default order): Function HexCode(Cell As Range) As String Dim NumberInHex As String Let NumberInHex = Hex(Cell.Interior.Color) 'Hex gives Interior.color number in hexadecimal HexCode = Right("000000" & NumberInHex, 6) ' This part ensures that 6 digits are given (with a extra zero at the start if the B number is less than 15 (or f) End Function 'This returns the hex code in RGB order Function getRGB1(rcell As Range) As String Dim NumberInHex As String Let NumberInHex = Hex(rcell.Interior.Color) 'Hex gives Interior.color number in hexadecimal Dim sColor As String Let sColor = Right("000000" & NumberInHex, 6) ' This part ensures that 6 digits are given (with a extra zero at the start if the B number is less than 15 (or f) getRGB1 = Right(sColor, 2) & Mid(sColor, 3, 2) & Left(sColor, 2) 'Chops up the 6 digits into 3 lots of 2 in R G B order End Function 'This will return the RGB value in the cell using a worksheet function: Function getRGB2(rcell As Range) As String Dim C As Long Dim R As Long Dim G As Long Dim B As Long C = rcell.Interior.Color R = C Mod 256 'same as to my code 2: Mod gives what is left over from RGBColour after dividing by 256 : All .color numbers are = (multiples of 256 for green and Blue: (256,512....(255 x 256)) for green ; (65536,131072.....(255 x 65536) for Blue)) + (0 to 255 depending on the Rot value)). So here the Mod blocks out Green and Blue multiples, and leaves just the 0 - 255 step 1 integer which represents Red G = C \ 256 Mod 256 ' Similar to my code 2 : \ 256 blocks out the Red Bit and Mod 256 blocks out the Blue Bit B = C \ 65536 'Mod 256 ' Similar to my code 2 : \ 65536 gives the number of B (Mod is not necerssary) getRGB2 = "R=" & R & ", G=" & G & ", B=" & B End Function Sub TestNeonRedSharpie() ' Dim HexStuff1 As Variant Let HexStuff1 = HexCode(Range("A1")) Debug.Print HexStuff1 ' Dim HexStuff2 As Variant Let HexStuff2 = getRGB1(Cells(1, 1)) Debug.Print HexStuff2 ' Dim HexStuff3 As Variant Let HexStuff3 = getRGB2(Cells(1, 1)) Debug.Print HexStuff3 End Sub 'TestNeonRedSharpie()

It is all useful stuff for a beginner (Note your last function is similar to my code 2, and a small correction… I do not think you need the Mod 256 bit)

Alan

P.s.
. I am still interested in an explanation of what is happening in my code 3, especially the And bit, if anyone has any ideas.

DocAElstein · Jul 31, 2014

Hi,
. Just asking again for any ideas on my main problem:- That is I still do not understand my last code (Code 3), that is to say it is not clear to me what And is doing in this case…
…Any fresh ideas?? I just do not understand what the And does when applied to 2 numbers. As I mentioned, I have no problem using and understanding And in typical conditional statements.. That is to say typical lines like
. If a=1 And b=2 Then …. Etc.

Thanks

shg · Jul 31, 2014

The VBA And operator does a bitwise Boolean And on two 32-bit integers (i.e., Longs). The value of vbBlue is hex FF0000, so if you and that with some random color, you get just the blue component.

DocAElstein · Jul 31, 2014

shg said:
The VBA And operator does a bitwise Boolean And on two 32-bit integers (i.e., Longs). The value of vbBlue is hex FF0000, so if you and that with some random color, you get just the blue component.

Hi shg,
. Great. Thanks. I get it now.
. I’m very grateful.
Alan.

P.s. Revised Code 3 below with comments for anyone stumbling on this Thread in the future. ( Note once you understand wot is going on you can see that a normal divide / is sufficient for this code, and not the integer divide \ which is necessary for Code 2)

'_______________________________________________________________________________________- '_______________________________________________________________________________ '__________________________________________________________________________________ '______________________________________________________________ ' Code 3 Sub FindInteriorColorRGBColourIndexUsingHexStuff3() ****Dim R As Byte, G As Byte, B As Byte, RGBColour As Long ****RGBColour = ActiveSheet.Cells(1, 1).Interior.Color ****R = RGBColour And vbRed 'vbRed = 255 or in hex 0000FF.** And does a Boolean And for every Bit in the hexadecimal format.**So for vbRed (0000FF) And RGBColor the And function returns the Red Part of the number as this is the only part that is in both of the numbers! This will be a number from 0 to 255 ****G = (RGBColour And vbGreen) / &H100 'vbGreen = 255 x 256 = 65280 or 00FF00 in Hex ; &H100 = (256 x 1) + (16 x 0) + (1 x 0) = 256.**And does a Boolean And for every Bit in the hexadecimal format.**So for vbgreen (00FF00) And RGBColor the And function returns the green Part of the number as this is the only part that is in both of the numbers! This will be a number from 0 to 65280. Dividing this number by 256 gives the integer G value ****B = (RGBColour And vbBlue) / &H10000 'vbBlue = (256^2) x 255 = 16711680 or FF0000 in Hex ; &H10000 = (65536 x 1) + (4096 x 1) + (256 x 0) + (16 x 0) + (1 x 0) = 65536.**And does a Boolean And for every Bit in the hexadecimal format.**So for vbBlue (FF0000) And RGBColor the And function returns the Blue Part of the number as this is the only part that is in both of the numbers! This will be a number from 0 to 16712194. Dividing this number by 65536 gives the integer B value ** MsgBox ("R" & vbTab & "G" & vbTab & "B" & vbTab & "RGBColour is " & R & vbTab & G & vbTab & B & vbTab & RGBColour) End Sub 'FindInteriorColorRGBColourIndexUsingHexStuff3() '_______________________________________________________________________________ '__________________________________________________________________________________ '______________________________________________________________

DocAElstein · Jul 31, 2014

Code again without the **** Bug which cak´me in because i edited the thread!!!

'_______________________________________________________________________________________- '_______________________________________________________________________________ '__________________________________________________________________________________ '______________________________________________________________ ' Code 3 Sub FindInteriorColorRGBColourIndexUsingHexStuff3() Dim R As Byte, G As Byte, B As Byte, RGBColour As Long RGBColour = ActiveSheet.Cells(1, 1).Interior.Color R = RGBColour And vbRed 'vbRed = 255 or in hex 0000FF. And does a Boolean And for every Bit in the hexadecimal format. So for vbRed (0000FF) And RGBColor the And function returns the Red Part of the number as this is the only part that is in both of the numbers! This will be a number from 0 to 255 G = (RGBColour And vbGreen) / &H100 'vbGreen = 255 x 256 = 65280 or 00FF00 in Hex ; &H100 = (256 x 1) + (16 x 0) + (1 x 0) = 256. And does a Boolean And for every Bit in the hexadecimal format. So for vbgreen (00FF00) And RGBColor the And function returns the green Part of the number as this is the only part that is in both of the numbers! This will be a number from 0 to 65280. Dividing this number by 256 gives the integer G value B = (RGBColour And vbBlue) / &H10000 'vbBlue = (256^2) x 255 = 16711680 or FF0000 in Hex ; &H10000 = (65536 x 1) + (4096 x 1) + (256 x 0) + (16 x 0) + (1 x 0) = 65536. And does a Boolean And for every Bit in the hexadecimal format. So for vbBlue (FF0000) And RGBColor the And function returns the Blue Part of the number as this is the only part that is in both of the numbers! This will be a number from 0 to 16712194. Dividing this number by 65536 gives the integer B value MsgBox ("R" & vbTab & "G" & vbTab & "B" & vbTab & "RGBColour is " & R & vbTab & G & vbTab & B & vbTab & RGBColour) End Sub 'FindInteriorColorRGBColourIndexUsingHexStuff3() '_______________________________________________________________________________ '__________________________________________________________________________________ '______________________________________________________________

shg · Jul 31, 2014

I'm more likely to read your code in future posts if it's in code tags, which, curiously, is suggested in your signature.

Four-point font in Technicolor attracts me not.

DocAElstein · Aug 1, 2014

shg said:
I'm more likely to read your code in future posts if it's in code tags, which, curiously, is suggested in your signature.

Four-point font in Technicolor attracts me not.

Hi shg,
. Thanks again for that feedback- I appreciate it. I was already thinking along the lines you said - that my codes were a bit over the top for a profi to read, and was trying to simplify it… at least to remove the long rambling comments…I had already asked about that http://www.mrexcel.com/forum/about-board/795476-comments-code.html but no reply to date.
. I first made the effort to get into the HTML Code stuff, rather than simple Code tags after seeing and hearing about it from other moderators! (http://www.mrexcel.com/forum/questi...rch-workbooks-oder-windows-oop-methode-2.html http://www.mrexcel.com/forum/about-board/780464-re-text-format-forums-o00o-`-_-`-o00o.html ). But they also suggest simple Code Tags as well as the HTML stuff!!
. Lots of comments I thought was useful for other normal users and beginners like me viewing the thread at some time later, but again a pain for some Profis. Can’t please everyone, all the time!. But I’ll try to keep simple short no comment codes in Threads when you are on line.
Thanks again
Alan

P.s last couple of simple Codes (Codes 2 and 3) then for getting the R G B values for the interior color of a cell

' Code 3

Code:

Sub FindInteriorColorRGBColourIndexUsingHexStuff3NoComments()
    Dim R As Byte, G As Byte, B As Byte, RGBColour As Long
    RGBColour = ActiveSheet.Cells(1, 1).Interior.Color
    R = RGBColour And vbRed
    G = (RGBColour And vbGreen) / &H100
    B = (RGBColour And vbBlue) / &H10000
   MsgBox ("R" & vbTab & "G" & vbTab & "B" & vbTab & "RGBColour is " & R & vbTab & G & vbTab & B & vbTab & RGBColour)
End Sub

' Code 2

Code:

Sub FindInteriorColorRGBColourIndexUsingHexStuff2NoComments()
Dim R As Byte, G As Byte, B As Byte, RGBColour As Long
    RGBColour = ActiveSheet.Cells(1, 1).Interior.Color
    R = RGBColour Mod 256
    G = (RGBColour Mod 256 ^ 2) \ 256
    B = RGBColour \ (256 ^ 2)
 MsgBox ("R" & vbTab & "G" & vbTab & "B" & vbTab & "RGBColour is " & R & vbTab & G & vbTab & B & vbTab & RGBColour)
End Sub

All codes again (with and without comments!) here:

FileSnack | Easy file sharing

FileSnack | Easy file sharing

Have a Great day

Interior.Color RGB Values

DocAElstein

Banned user

Excel Facts

NeonRedSharpie

Well-known Member

DocAElstein

Banned user

DocAElstein

Banned user

shg

MrExcel MVP

DocAElstein

Banned user

DocAElstein

Banned user

shg

MrExcel MVP

DocAElstein

Banned user

Similar threads

Forum statistics

Share this page

Interior.Color RGB Values

Banned user

Excel Facts

Well-known Member

Banned user

Banned user

MrExcel MVP

Banned user

Banned user

MrExcel MVP

Banned user

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock