Error in VBA Sub Colors() lines of code

[quemuenchatocha]

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
	A	B	C	D	E	F	G
1	Color	Color Index	Hexadecimal	R	G	B	Color Code
2		1	#000000	0	0	0	16777215
3		2	#FFFFFF	255	255	255	16777215
4		3	#FF0000	255	0	0	16777215
5		4	#00FF00	0	255	0	16777215
6		5	#0000FF	0	0	255	16777215
7		6	#FFFF00	255	255	0	16777215
8		7	#FF00FF	255	0	255	16777215
9		8	#00FFFF	0	255	255	16777215
10		9	#800000	128	0	0	16777215
11		10	#008000	0	128	0	16777215
12		11	#000080	0	0	128	16777215
13		12	#808000	128	128	0	16777215
14		13	#800080	128	0	128	16777215
15		14	#008080	0	128	128	16777215
16		15	#C0C0C0	192	192	192	16777215
17		16	#808080	128	128	128	16777215
18		17	#9999FF	153	153	255	16777215
19		18	#993366	153	51	102	16777215
20		19	#FFFFCC	255	255	204	16777215
21		20	#CCFFFF	204	255	255	16777215
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]

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

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

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.

 

[BSALV]

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 '

cells(i + 1, 3) = "'" & str1

2nd question, swap these 2 lines, because first you ask its color and then you change it, .... ????

 ColorVal = Cells(i + 1, 1).Interior.Color
  Cells(i + 1, 1).Interior.ColorIndex = i

 

[6StringJazzer]

when I run the line

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

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:

  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.

 

[quemuenchatocha]

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 '

cells(i + 1, 3) = "'" & str1

2nd question, swap these 2 lines, because first you ask its color and then you change it, .... ????

 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.

 

[quemuenchatocha]

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:

  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.

 

[Fluff]

BSALV has already shown you how to do that, by using

  Cells(i + 1, 3) = "'" & str1

 

[quemuenchatocha]

BSALV has already shown you how to do that, by using

  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!

 

[Fluff]

That's certainly the simplest way of doing it.