Hi,

I am trying to construct quite a complete worksheet that will utilise the
VBA code I have found elsewhere in the discussion groups and which will allow
me to have more than three instances of conditional formatting per cell (five
is my aim!)

I know which five colours I need but don't know how to identify the VBA code
for these colours that I will need to use. Can anyone tell me how I can
identify the code for these colours, please? I though that the worksheet
function CELL("color") might do this but it doesn't work as it just returns a
zero value if the cell is shaded.

This will at least give me a starting point for what I would wish to move on
to (look out for further postings when I get to that!)

Many thanks in anticipation,

Dickie

Dickie Worton wrote:
Hi,

I am trying to construct quite a complete worksheet that will utilise the
VBA code I have found elsewhere in the discussion groups and which will allow
me to have more than three instances of conditional formatting per cell (five
is my aim!)

I know which five colours I need but don't know how to identify the VBA code
for these colours that I will need to use. Can anyone tell me how I can
identify the code for these colours, please? I though that the worksheet
function CELL("color") might do this but it doesn't work as it just returns a
zero value if the cell is shaded.



You could just RECORD a Macro while you color cells, then look at it to see
the values
given
This will at least give me a starting point for what I would wish to move on
to (look out for further postings when I get to that!)

Many thanks in anticipation,

Dickie

--
Message posted via http://www.officekb.com

Hi
Colour a cell then run
Sub tester()
Msgbox ActiveCell.Interior.ColorIndex
End sub

run this for each of your colours.
regards
Paul

On May 25, 12:17 pm, Dickie Worton
wrote:
Hi,

I am trying to construct quite a complete worksheet that will utilise the
VBA code I have found elsewhere in the discussion groups and which will allow
me to have more than three instances of conditional formatting per cell (five
is my aim!)

I know which five colours I need but don't know how to identify the VBA code
for these colours that I will need to use. Can anyone tell me how I can
identify the code for these colours, please? I though that the worksheet
function CELL("color") might do this but it doesn't work as it just returns a
zero value if the cell is shaded.

This will at least give me a starting point for what I would wish to move on
to (look out for further postings when I get to that!)

Many thanks in anticipation,

Dickie

this site lists all the available colors & codes for windows..........

http://www.mvps.org/dmcritchie/excel/colors.htm

:)
susan


On May 25, 7:27 am, "Francois via OfficeKB.com" <u18959@uwe wrote:
Dickie Worton wrote:
Hi,

I am trying to construct quite a complete worksheet that will utilise the
VBA code I have found elsewhere in the discussion groups and which will allow
me to have more than three instances of conditional formatting per cell (five
is my aim!)

I know which five colours I need but don't know how to identify the VBA code
for these colours that I will need to use. Can anyone tell me how I can
identify the code for these colours, please? I though that the worksheet
function CELL("color") might do this but it doesn't work as it just returns a
zero value if the cell is shaded.

You could just RECORD a Macro while you color cells, then look at it to see
the values
given

This will at least give me a starting point for what I would wish to move on
to (look out for further postings when I get to that!)

Many thanks in anticipation,

Dickie

--
Message posted viahttp://www.officekb.com

Public Enum xlColorIndex
xlCIBlack = 1
xlCIWhite = 2
xlCIRed = 3
xlCIBrightGreen = 4
xlCIBlue = 5
xlCIYellow = 6
xlCIPink = 7
xlCITurquoise = 8
xlCIDarkRed = 9
xlCIGreen = 10
xlCIDarkBlue = 11
xlCIDarkYellow = 12
xlCIViolet = 13
xlCITeal = 14
xlCIGray25 = 15
xlCIGray50 = 16
xlCIPeriwinkle = 17
xlCIPlum = 18
xlCIIvory = 19
xlCILightTurquoise = 20
xlCIDarkPurple = 21
xlCICoral = 22
xlCIOceanBlue = 23
xlCIIceBlue = 24
'xlCIDarkBlue = 25
'xlCIPink = 26
'xlCIYellow = 27
'xlCITurquoise = 28
'xlCIViolet = 29
'xlCIDarkRed = 30
'xlCITeal = 31
'xlCIBlue = 32
xlCISkyBlue = 33
xlCILightGreen = 35
xlCILightYellow = 36
xlCIPaleBlue = 37
xlCIRose = 38
xlCILavender = 39
xlCITan = 40
xlCILightBlue = 41
xlCIAqua = 42
xlCILime = 43
xlCIGold = 44
xlCILightOrange = 45
xlCIOrange = 46
xlCIBlueGray = 47
xlCIGray40 = 48
xlCIDarkTeal = 49
xlCISeaGreen = 50
xlCIDarkGreen = 51
xlCIBrown = 53
xlCIIndigo = 55
xlCIGray80 = 56
End Enum


'----------------


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dickie Worton" wrote in message
...
Hi,

I am trying to construct quite a complete worksheet that will utilise the
VBA code I have found elsewhere in the discussion groups and which will
allow
me to have more than three instances of conditional formatting per cell
(five
is my aim!)

I know which five colours I need but don't know how to identify the VBA
code
for these colours that I will need to use. Can anyone tell me how I can
identify the code for these colours, please? I though that the worksheet
function CELL("color") might do this but it doesn't work as it just
returns a
zero value if the cell is shaded.

This will at least give me a starting point for what I would wish to move
on
to (look out for further postings when I get to that!)

Many thanks in anticipation,

Dickie

Hi Dickie
Code for colours start from 0 for no color to 56. The range is the same as
the colour pallete with a few extras thrown in. Also some of the colours are
repeated. The list is:
0 no colour
1 black
2 white
3 Red
4 Bright Green
5 Blue
6 Yellow
7 Pink
8 Turquose
9 Dark Red
10 Green
11 Blue
12 Dark Yellow
13 Violet
14 Teal
15 Gray 25%
16 Gray 50%
17 Light Royal Blue
18 Plum
19 Pale Yellow
20 Light Turquose
21 Dark Violet
22 Dark Crimson
23 Light Blue
24 Very light Blue
25 Dark Blue
26 Pink
27 Yellow
28 Turquose
29 Violet
30 Dark Red
31 Teal
32 Blue
33 Sky Blue
34 Light Turquose
35 Light Green
36 Light Yellow
37 Pale Blue
38 Rose
39 Lavender
40 Tan
41 Light Blue
42 Aqua
43 Lime
44 Gold
45 Light Orange
46 Orange
47 Blue-Gray
48 Gray 40%
49 Dark Teal
50 Sea Green
51 Dark Green
52 Olive Green
53 Brown
54 Plum
55 Indigo
56 Gray 80%

"Dickie Worton" wrote:

Hi,

I am trying to construct quite a complete worksheet that will utilise the
VBA code I have found elsewhere in the discussion groups and which will allow
me to have more than three instances of conditional formatting per cell (five
is my aim!)

I know which five colours I need but don't know how to identify the VBA code
for these colours that I will need to use. Can anyone tell me how I can
identify the code for these colours, please? I though that the worksheet
function CELL("color") might do this but it doesn't work as it just returns a
zero value if the cell is shaded.

This will at least give me a starting point for what I would wish to move on
to (look out for further postings when I get to that!)

Many thanks in anticipation,

Dickie

I had to do something similar and used the following code -
What this did was to scan a series of cells and record the colour of the
cell and the font. I then used these in another part of te macro to "paste"
the font colour and cell fill colour recorded previously.

This just records the cell and font coulour -
ReDim FONT_COLOUR(NUM_CODES) As Integer

For N = 0 To NUM_CODES
CELL_COLOUR(N) = Worksheets("Leave Codes").Cells(N + 2,
2).Interior.ColorIndex
FONT_COLOUR(N) = Worksheets("Leave Codes").Cells(N + 2,
2).Font.ColorIndex
Next N

This essentially pastes the required font colour and fill colour on to a
selection. N defines the particular cell and font colour combination
required. Instad of areas you coiuld use cells, I tink it should still work.

Set rangeToUse = Selection
For Each singleArea In rangeToUse.Areas
singleArea.Value = LEAVE_CODES(N)
singleArea.Font.ColorIndex = FONT_COLOUR(N)
With Selection.Interior
.ColorIndex = CELL_COLOUR(N)
.Pattern = xlSolid
End With

Hope it is of some help
Rick

wrote in message
ups.com...
Hi
Colour a cell then run
Sub tester()
Msgbox ActiveCell.Interior.ColorIndex
End sub

run this for each of your colours.
regards
Paul

On May 25, 12:17 pm, Dickie Worton
wrote:
Hi,

I am trying to construct quite a complete worksheet that will utilise the
VBA code I have found elsewhere in the discussion groups and which will
allow
me to have more than three instances of conditional formatting per cell
(five
is my aim!)

I know which five colours I need but don't know how to identify the VBA
code
for these colours that I will need to use. Can anyone tell me how I can
identify the code for these colours, please? I though that the worksheet
function CELL("color") might do this but it doesn't work as it just
returns a
zero value if the cell is shaded.

This will at least give me a starting point for what I would wish to move
on
to (look out for further postings when I get to that!)

Many thanks in anticipation,

Dickie

Thank you all very much, I knew that someone would know!

Kind regards,

Dickie

P.S. How fabulous are these discussion groups?
And how & why did I struggle for so long without them?

"John D" wrote:

Hi Dickie
Code for colours start from 0 for no color to 56. The range is the same as
the colour pallete with a few extras thrown in. Also some of the colours are
repeated. The list is:
0 no colour
1 black
2 white
3 Red
4 Bright Green
5 Blue
6 Yellow
7 Pink
8 Turquose
9 Dark Red
10 Green
11 Blue
12 Dark Yellow
13 Violet
14 Teal
15 Gray 25%
16 Gray 50%
17 Light Royal Blue
18 Plum
19 Pale Yellow
20 Light Turquose
21 Dark Violet
22 Dark Crimson
23 Light Blue
24 Very light Blue
25 Dark Blue
26 Pink
27 Yellow
28 Turquose
29 Violet
30 Dark Red
31 Teal
32 Blue
33 Sky Blue
34 Light Turquose
35 Light Green
36 Light Yellow
37 Pale Blue
38 Rose
39 Lavender
40 Tan
41 Light Blue
42 Aqua
43 Lime
44 Gold
45 Light Orange
46 Orange
47 Blue-Gray
48 Gray 40%
49 Dark Teal
50 Sea Green
51 Dark Green
52 Olive Green
53 Brown
54 Plum
55 Indigo
56 Gray 80%

"Dickie Worton" wrote:

Hi,

I am trying to construct quite a complete worksheet that will utilise the
VBA code I have found elsewhere in the discussion groups and which will allow
me to have more than three instances of conditional formatting per cell (five
is my aim!)

I know which five colours I need but don't know how to identify the VBA code
for these colours that I will need to use. Can anyone tell me how I can
identify the code for these colours, please? I though that the worksheet
function CELL("color") might do this but it doesn't work as it just returns a
zero value if the cell is shaded.

This will at least give me a starting point for what I would wish to move on
to (look out for further postings when I get to that!)

Many thanks in anticipation,

Dickie

Have a look in VBA help under "colorindex".



Dickie Worton wrote:

Hi,

I am trying to construct quite a complete worksheet that will utilise the
VBA code I have found elsewhere in the discussion groups and which will allow
me to have more than three instances of conditional formatting per cell (five
is my aim!)

I know which five colours I need but don't know how to identify the VBA code
for these colours that I will need to use. Can anyone tell me how I can
identify the code for these colours, please? I though that the worksheet
function CELL("color") might do this but it doesn't work as it just returns a
zero value if the cell is shaded.

This will at least give me a starting point for what I would wish to move on
to (look out for further postings when I get to that!)

Many thanks in anticipation,

Dickie


--
Gordon Rainsford

London UK

In VBA help, type 'PatternColorIndex Property' in the search box then click
on it when the menu comes up. It displays the palette with the corresponding
numbers.

"Dickie Worton" wrote:

Hi,

I am trying to construct quite a complete worksheet that will utilise the
VBA code I have found elsewhere in the discussion groups and which will allow
me to have more than three instances of conditional formatting per cell (five
is my aim!)

I know which five colours I need but don't know how to identify the VBA code
for these colours that I will need to use. Can anyone tell me how I can
identify the code for these colours, please? I though that the worksheet
function CELL("color") might do this but it doesn't work as it just returns a
zero value if the cell is shaded.

This will at least give me a starting point for what I would wish to move on
to (look out for further postings when I get to that!)

Many thanks in anticipation,

Dickie