# colour codes

#### Jasonraul

##### Board Regular
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
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
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
' 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

Hello hiker95
Excellent and thanks for your help
Jason

#### Biz

##### Well-known Member
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
' 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
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
' Original code by jim may has been modified.
' jim may
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.

Replies
3
Views
81
Replies
3
Views
153
Replies
4
Views
82
Replies
3
Views
45
Replies
3
Views
84

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

### 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?

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