quemuenchatocha
Board Regular
- Joined
- Aug 4, 2021
- Messages
- 50
- Office Version
- 365
- 2019
- Platform
- Windows
Dear cordial greetings
I am learning the basics of Excel's VBA language, and I have come across the issue related to the default color range. Investigating in different sources I have found a code that allows to obtain the values for the RGB primary color palette, presenting them all together [Column C], or separately [Columns D:F] (the credits of the function correspond to Mr. Allen Wyatt, Determining the RGB Value of a Color).
Now, my question is directed to obtain the values recorded in column C, separately in columns H:J.
I have already obtained the respective values for column H (Color "R") and column I (Color "H"), through the implementation of combined functions, but I have found it difficult to obtain the last value for Color "B", which goes in column J.
I have tried various combinations with the functions RIGHT, MID, FIND, but have not obtained any satisfactory result. I apologize in advance for the length of the statement, and I appreciate any advice regarding how to obtain these last characters of the cell.
Thank you for your attention.
P.S.: In case the VBA functions are needed, here are the codes I use
I am learning the basics of Excel's VBA language, and I have come across the issue related to the default color range. Investigating in different sources I have found a code that allows to obtain the values for the RGB primary color palette, presenting them all together [Column C], or separately [Columns D:F] (the credits of the function correspond to Mr. Allen Wyatt, Determining the RGB Value of a Color).
Now, my question is directed to obtain the values recorded in column C, separately in columns H:J.
I have already obtained the respective values for column H (Color "R") and column I (Color "H"), through the implementation of combined functions, but I have found it difficult to obtain the last value for Color "B", which goes in column J.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C27 | C2 | =getRGB1(A2) |
| D2:D27 | D2 | =getRGB2(A2,1) |
| E2:E27 | E2 | =getRGB2(A2,2) |
| F2:F27 | F2 | =getRGB2(A2,3) |
| H2:H27 | H2 | =NUMBERVALUE(MID(LEFT(C2,FIND(",",C2)-1),FIND("(",C2)+1,LEN(C2))) |
| I2:I27 | I2 | =NUMBERVALUE(MID(C2,FIND(",",C2)+1,FIND(",",C2,FIND(",",C2)+1)-FIND(",",C2)-1)) |
I have tried various combinations with the functions RIGHT, MID, FIND, but have not obtained any satisfactory result. I apologize in advance for the length of the statement, and I appreciate any advice regarding how to obtain these last characters of the cell.
Thank you for your attention.
P.S.: In case the VBA functions are needed, here are the codes I use
VBA Code:
Sub ColorRef()
Dim x As Integer
For x = 1 To 56
If x <= 57 Then
ActiveCell(x, 1).Interior.ColorIndex = x
ActiveCell(x, 2) = x
End If
Next x
End Sub
VBA Code:
Function getRGB1(rcell) As String
Dim ColorVal As Long
Dim R As Long
Dim G As Long
Dim B As Long
ColorVal = rcell.Interior.Color
R = ColorVal Mod 256
G = ColorVal \ 256 Mod 256
B = ColorVal \ 65536 Mod 256
getRGB1 = "GRB(" & R & "," & G & "," & B & ")"
End Function
VBA Code:
Function getRGB2(rcell As Range, Optional opt As Integer) As Long
Dim ColorVal As Long
Dim R As Long
Dim G As Long
Dim B As Long
ColorVal = rcell.Interior.Color
R = ColorVal Mod 256
G = ColorVal \ 256 Mod 256
B = ColorVal \ 65536 Mod 256
If opt = 1 Then
getRGB2 = R
ElseIf opt = 2 Then
getRGB2 = G
ElseIf opt = 3 Then
getRGB2 = B
Else
getRGB2 = ColorVal
End If
End Function
