Error in VBA Sub Colors() lines of code

quemuenchatocha

Board Regular
Joined
Aug 4, 2021
Messages
50
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Dear all, have a very happy beginning of the year.

I am carrying out a code in VBA, I am novice in these subjects, the idea that I am trying to develop in the worksheet 1 is to show the different options of colors that Excel has available (or at least of which until the moment I have knowledge). To do this from the color [Column A], I identify the Color Index [Column B], traditional hexadecimal code - six characters [Column C], the decimal values of the RGB color palette [Column D:F] and the Color code [Column G].

Ejemplo_1.xlsm
ABCDEFG
1ColorColor IndexHexadecimalRGBColor Code
21#00000000016777215
32#FFFFFF25525525516777215
43#FF00002550016777215
54#00FF000255016777215
65#0000FF0025516777215
76#FFFF00255255016777215
87#FF00FF255025516777215
98#00FFFF025525516777215
109#8000001280016777215
1110#0080000128016777215
1211#0000800012816777215
1312#808000128128016777215
1413#800080128012816777215
1514#008080012812816777215
1615#C0C0C019219219216777215
1716#80808012812812816777215
1817#9999FF15315325516777215
1918#9933661535110216777215
2019#FFFFCC25525520416777215
2120#CCFFFF20425525516777215
Hoja2


The code I have so far is the following, which has been slightly modified from its original version [Color Palette and the 56 Excel ColorIndex Colors]

VBA Code:
Sub Colors()
Application.ScreenUpdating = False
Dim i As Long
Dim str0 As String, str1 As Variant
Dim ColorVal As Variant

Cells(1, 1).Value = "Color"
Cells(1, 2).Value = "Color Index"
Cells(1, 3).Value = "Hexadecimal"
Cells(1, 4).Value = "R"
Cells(1, 4).Font.Color = vbRed
Cells(1, 5).Value = "G"
Cells(1, 5).Font.Color = vbGreen
Cells(1, 6).Value = "B"
Cells(1, 6).Font.Color = vbBlue
Cells(1, 7).Value = "Color Code"

For i = 1 To 56
  
  ColorVal = Cells(i + 1, 1).Interior.Color
  
  Cells(i + 1, 1).Interior.ColorIndex = i
  Cells(i + 1, 2).Font.ColorIndex = i
  Cells(i + 1, 2).Value = i
  
  str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6)
  str1 = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2)
  
  Cells(i + 1, 3) = "#" & str1
  Cells(i + 1, 4) = CLng("&H" & Right(str0, 2))
  Cells(i + 1, 5) = CLng("&H" & Mid(str0, 3, 2))
  Cells(i + 1, 6) = CLng("&H" & Left(str0, 2))
  Cells(i + 1, 7) = ColorVal

Next i
End Sub

I have two problems when running the code. The first one is that when I run the line
VBA Code:
Cells(i + 1, 3) = "#" & str1
, if I don't prefix the character "#", I get an error in the results obtained, that is, I don't get the six characters that this line is supposed to return.

The second is related to the line
VBA Code:
Cells(i + 1, 7) = ColorVal
, when I execute it I get the value corresponding to the color located in cell A3 [White] in the range G2:G57, which should not work that way, but if I execute the code one more time (Again F5), I get the expected (correct) results in the range G2:G57.

I appreciate your kind attention and guidance in resolving my concerns.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
VBA Code:
cells(i + 1, 3) = "#" & str1
without "#", excel helps you by eliminating the zeros at the beginning.
Or you have to make those cells format "text" or, if you don't want that "#" replace that with a single '
VBA Code:
cells(i + 1, 3) = "'" & str1

2nd question, swap these 2 lines, because first you ask its color and then you change it, .... ????
VBA Code:
 ColorVal = Cells(i + 1, 1).Interior.Color
  Cells(i + 1, 1).Interior.ColorIndex = i
 
Last edited:
Upvote 0
Solution
when I run the line
VBA Code:
Cells(i + 1, 3) = "#" & str1
, if I don't prefix the character "#", I get an error in the results obtained, that is, I don't get the six characters that this line is supposed to return.
What do you get instead? If your color code does not have any hex digits (A-F) then Excel will interpret it as a decimal integer and you lose any leading zeroes. One way around this is to format the column as Text, or do exactly what you did.




The second is related to the line
VBA Code:
Cells(i + 1, 7) = ColorVal
, when I execute it I get the value corresponding to the color located in cell A3 [White] in the range G2:G57, which should not work that way, but if I execute the code one more time (Again F5), I get the expected (correct) results in the range G2:G57.
You are retrieving the color of the cell in column A before you actually set the color, so it's always going to be "no color" the first time through:
VBA Code:
  ColorVal = Cells(i + 1, 1).Interior.Color
 
  Cells(i + 1, 1).Interior.ColorIndex = i
To fix that switch the order of the two lines of code above.
 
Upvote 0
VBA Code:
cells(i + 1, 3) = "#" & str1
without "#", excel helps you by eliminating the zeros at the beginning.
Or you have to make those cells format "text" or, if you don't want that "#" replace that with a single '
VBA Code:
cells(i + 1, 3) = "'" & str1

2nd question, swap these 2 lines, because first you ask its color and then you change it, .... ????
VBA Code:
 ColorVal = Cells(i + 1, 1).Interior.Color
  Cells(i + 1, 1).Interior.ColorIndex = i
Thank you very much for your valuable information, I am a novice in these matters and I am learning as I go along. Regarding the first question, I would like that instead of prefixing the # character to recognize all the six characters as text, in the cell where this value is registered, all the characters would appear without integer format, for example
000000
FFFFFFFF
008000
If you can give me some hint on how to make this possible I would appreciate it.
 
Upvote 0
What do you get instead? If your color code does not have any hex digits (A-F) then Excel will interpret it as a decimal integer and you lose any leading zeroes. One way around this is to format the column as Text, or do exactly what you did.





You are retrieving the color of the cell in column A before you actually set the color, so it's always going to be "no color" the first time through:
VBA Code:
  ColorVal = Cells(i + 1, 1).Interior.Color
 
  Cells(i + 1, 1).Interior.ColorIndex = i
To fix that switch the order of the two lines of code above.
You are very kind for your support, I will put your valuable information into practice, thanks for the help.
 
Upvote 0
BSALV has already shown you how to do that, by using
VBA Code:
  Cells(i + 1, 3) = "'" & str1
 
Upvote 0
BSALV has already shown you how to do that, by using
VBA Code:
  Cells(i + 1, 3) = "'" & str1
Ok, perfect, thank you very much for your valuable contributions, I thought that the use of some Type conversion functions would be useful in this case, but if that is the most optimal solution I will implement it, a big hug!
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top