Hi All,

This might be an interesting one for the experts out there...

I have an Excel sheet with 50 columns and 50 rows. Each cell is colour coded
(background colour). i.e. White, Black, Red etc there is no data in the
cells...

I need to go through each cell on every row and column and place a number in
the cell according to which background colour the cell has... i.e.

White = 0
Black = 1
Red = 2
etc...

Is somebody can help me on this it would be very much appreciated, else I'm
gonna have to go and type all this into a table by hand... <violins :(
</violins)

Thanks again,
Andy

For Each cell In Selection
Select Case cell.Interior.Colorindex
Case 3: cell.Value = 2
Case 1: cell.Value = 1
Case xlColorindexNone: cell.Value = 0
End Select
Next cell

Select all the cells then run the code

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"EV" wrote in message
...
Hi All,

This might be an interesting one for the experts out there...

I have an Excel sheet with 50 columns and 50 rows. Each cell is colour
coded
(background colour). i.e. White, Black, Red etc there is no data in the
cells...

I need to go through each cell on every row and column and place a number
in
the cell according to which background colour the cell has... i.e.

White = 0
Black = 1
Red = 2
etc...

Is somebody can help me on this it would be very much appreciated, else
I'm
gonna have to go and type all this into a table by hand... <violins :(
</violins)

Thanks again,
Andy

Sub test()
Dim i As Long, j As Long

With ActiveSheet
For j = 1 To 50
For i = 1 To 50
Select Case .Cells(i, j).Interior.Color
Case vbWhite: .Cells(i, j).Value = 0
Case vbBlack: .Cells(i, j).Value = 1
Case vbRed: .Cells(i, j).Value = 2
End Select
Next
Next
End With
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"EV" wrote in message
...
Hi All,

This might be an interesting one for the experts out there...

I have an Excel sheet with 50 columns and 50 rows. Each cell is colour
coded
(background colour). i.e. White, Black, Red etc there is no data in the
cells...

I need to go through each cell on every row and column and place a number
in
the cell according to which background colour the cell has... i.e.

White = 0
Black = 1
Red = 2
etc...

Is somebody can help me on this it would be very much appreciated, else
I'm
gonna have to go and type all this into a table by hand... <violins :(
</violins)

Thanks again,
Andy

"Bob Phillips" wrote in message
...

Select all the cells then run the code

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

You are an absolute star... Thank you for the quick reply...

It worked a treat...

Top man :)

Thanks
Andy

Is there a list of all the color indexes somwhere ... just so I can pick up
all the colors easily?

Thanks again
Andy

Look up Colorindex Property in VBA Help, and you will see them there.

Or else you could run this code snippet to see them. Run it on a blank
worksheet.

For i = 1 to 56
Cells(i,1).Interior.ColorIndex = i
Next

The row number gives the the index number

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"EV" wrote in message
...
Is there a list of all the color indexes somwhere ... just so I can pick
up
all the colors easily?

Thanks again
Andy

"Bob Phillips" wrote in message
...
Look up Colorindex Property in VBA Help, and you will see them there.

Or else you could run this code snippet to see them. Run it on a blank
worksheet.

For i = 1 to 56
Cells(i,1).Interior.ColorIndex = i
Next

The row number gives the the index number

--

that was pretty obvious really (duh !!! ) :)

Thanks Again... You been most helpfull...

Andy

Select the range and run this.

Sub NumbCol()
Dim c As Range
For Each c In Selection
c.Value = c.Interior.ColorIndex
Next c
End Sub


And to see all the colours with their colorindex

Sub ShowColor()
Dim c As Range, i As Integer
'select a cell in an empty column
Set c = ActiveCell
For i = 0 To 56
c.Offset(i, 0).Interior.ColorIndex = i
c.Offset(i, 1).Value = i
Next
End Sub


Regards Robert

"EV" wrote in message ...
Hi All,

This might be an interesting one for the experts out there...

I have an Excel sheet with 50 columns and 50 rows. Each cell is colour coded
(background colour). i.e. White, Black, Red etc there is no data in the
cells...

I need to go through each cell on every row and column and place a number in
the cell according to which background colour the cell has... i.e.

White = 0
Black = 1
Red = 2
etc...

Is somebody can help me on this it would be very much appreciated, else I'm
gonna have to go and type all this into a table by hand... <violins :(
</violins)

Thanks again,
Andy





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 21/02/2004