colour codes

[Jasonraul]

Hi

I have 1 workbook using 3 sheets.On sheet 1 if i have the cells displaying a certain symbol i want to then on sheet 2 show a different colour for each different symbol.Where can i find what each colours assigned number is...

Cheers

Jason

 

[hiker95]

Jasonraul,

See:

Sub RunColorIndex()
By jim may
http://www.mrexcel.com/forum/showthread.php?t=321553

 

[Jasonraul]

Thanks Hiker95

I will use a IF statement in Excel 2003 ..something as follows.. =IF(Training!M24="Y",1,0)+IF(Training!M24="N",2,0)....if sheets 1 (training) has a Y in the Cell H3 then on sheet 2 the H3 Cell will have a colour which corresponds to Y (green) or if it has a N (red) will be displayed and if the cell is empty then there is no colour.

Is there a number which corresponds to each colour such Green , Red , Blue etc..etc..

Cheers

Jason

 

[hiker95]

Jasonraul,

Is there a number which corresponds to each colour such Green , Red , Blue etc..etc..

I hope this will help you get started in the right direction.


Here are the results of the aforementioned macro:

Excel Workbook
	A	B
1	Interior.ColorIndex	Color
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	6	6
8	7	7
9	8	8
10	9	9
11	10	10
12	11	11
13	12	12
14	13	13
15	14	14
16	15	15
17	16	16
18	17	17
19	18	18
20	19	19
21	20	20
22	21	21
23	22	22
24	23	23
25	24	24
26	25	25
27	26	26
28	27	27
29	28	28
30	29	29
31	30	30
32	31	31
33	32	32
34	33	33
35	34	34
36	35	35
37	36	36
38	37	37
39	38	38
40	39	39
41	40	40
42	41	41
43	42	42
44	43	43
45	44	44
46	45	45
47	46	46
48	47	47
49	48	48
50
2003

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub RunColorIndex()
'
' jim may
' http://www.mrexcel.com/forum/showthread.php?t=321553
' Modified by stanleydgromjr
'
Dim i%  'As Integer
With Range("A1:B1")
  .Value = Array("Interior.ColorIndex", "Color")
  .HorizontalAlignment = xlCenter
End With
Range("A2").Select
For i% = 1 To 48
  With ActiveCell
    .Value = i%
    .HorizontalAlignment = xlCenter
  End With
  With ActiveCell.Offset(, 1)
    .Interior.ColorIndex = i%
    .Value = i%
    .HorizontalAlignment = xlCenter
  End With
  ActiveCell.Offset(1).Select
Next i
Range("A1:B1").ColumnWidth = 16
End Sub

Select a blank worksheet to make it active. Then run the RunColorIndex macro.

 

[Jasonraul]

Hello hiker95
Excellent and thanks for your help
Jason

 

[Biz]

Jasonraul,




I hope this will help you get started in the right direction.


Here are the results of the aforementioned macro:


2003

	A	B
1	Interior.ColorIndex	Color
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	6	6
8	7	7
9	8	8
10	9	9
11	10	10
12	11	11
13	12	12
14	13	13
15	14	14
16	15	15
17	16	16
18	17	17
19	18	18
20	19	19
21	20	20
22	21	21
23	22	22
24	23	23
25	24	24
26	25	25
27	26	26
28	27	27
29	28	28
30	29	29
31	30	30
32	31	31
33	32	32
34	33	33
35	34	34
36	35	35
37	36	36
38	37	37
39	38	38
40	39	39
41	40	40
42	41	41
43	42	42
44	43	43
45	44	44
46	45	45
47	46	46
48	47	47
49	48	48
50

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub RunColorIndex()
'
' jim may
' http://www.mrexcel.com/forum/showthread.php?t=321553
' Modified by stanleydgromjr
'
Dim i%  'As Integer
With Range("A1:B1")
  .Value = Array("Interior.ColorIndex", "Color")
  .HorizontalAlignment = xlCenter
End With
Range("A2").Select
For i% = 1 To 48
  With ActiveCell
    .Value = i%
    .HorizontalAlignment = xlCenter
  End With
  With ActiveCell.Offset(, 1)
    .Interior.ColorIndex = i%
    .Value = i%
    .HorizontalAlignment = xlCenter
  End With
  ActiveCell.Offset(1).Select
Next i
Range("A1:B1").ColumnWidth = 16
End Sub

Select a blank worksheet to make it active. Then run the RunColorIndex macro.

Should "48" be actually be "56". There are 56 colours in Excel 2003.

Biz

 

[hiker95]

Biz,

Nice catch.


Here are the results of the aforementioned updated macro:

Excel Workbook
	A	B
1	Interior.ColorIndex	Color
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	6	6
8	7	7
9	8	8
10	9	9
11	10	10
12	11	11
13	12	12
14	13	13
15	14	14
16	15	15
17	16	16
18	17	17
19	18	18
20	19	19
21	20	20
22	21	21
23	22	22
24	23	23
25	24	24
26	25	25
27	26	26
28	27	27
29	28	28
30	29	29
31	30	30
32	31	31
33	32	32
34	33	33
35	34	34
36	35	35
37	36	36
38	37	37
39	38	38
40	39	39
41	40	40
42	41	41
43	42	42
44	43	43
45	44	44
46	45	45
47	46	46
48	47	47
49	48	48
50	49	49
51	50	50
52	51	51
53	52	52
54	53	53
55	54	54
56	55	55
57	56	56
58
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub RunColorIndex56()
' hiker95, 06/16/2012
' http://www.mrexcel.com/forum/showthread.php?640896-colour-codes
' Original code by jim may has been modified.
' jim may
' http://www.mrexcel.com/forum/showthread.php?t=321553
Dim i As Long
With Range("A1:B1")
  .Value = Array("Interior.ColorIndex", "Color")
  .HorizontalAlignment = xlCenter
End With
Range("A2").Select
For i = 1 To 56
  With ActiveCell
    .Value = i
    .HorizontalAlignment = xlCenter
  End With
  With ActiveCell.Offset(, 1)
    .Interior.ColorIndex = i
    .Value = i
    .HorizontalAlignment = xlCenter
  End With
  ActiveCell.Offset(1).Select
Next i
Range("A1:B1").ColumnWidth = 16
End Sub

Select a blank worksheet to make it active.


Then run the RunColorIndex56 macro.