colour codes

Jasonraul

Board Regular
Joined
May 24, 2012
Messages
111
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Jasonraul

Board Regular
Joined
May 24, 2012
Messages
111
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
 
Last edited:

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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
AB
1Interior.ColorIndexColor
211
322
433
544
655
766
877
988
1099
111010
121111
131212
141313
151414
161515
171616
181717
191818
201919
212020
222121
232222
242323
252424
262525
272626
282727
292828
302929
313030
323131
333232
343333
353434
363535
373636
383737
393838
403939
414040
424141
434242
444343
454444
464545
474646
484747
494848
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.


Code:
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

Board Regular
Joined
May 24, 2012
Messages
111

ADVERTISEMENT

Hello hiker95
Excellent and thanks for your help
Jason
 

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,692
Office Version
  1. 2010
Platform
  1. Windows
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.


Code:
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Biz,

Nice catch.


Here are the results of the aforementioned updated macro:


Excel Workbook
AB
1Interior.ColorIndexColor
211
322
433
544
655
766
877
988
1099
111010
121111
131212
141313
151414
161515
171616
181717
191818
201919
212020
222121
232222
242323
252424
262525
272626
282727
292828
302929
313030
323131
333232
343333
353434
363535
373636
383737
393838
403939
414040
424141
434242
444343
454444
464545
474646
484747
494848
504949
515050
525151
535252
545353
555454
565555
575656
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.


Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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
Top