An interesting one for all you experts...

  • Thread starter Thread starter EV
  • Start date Start date
E

EV

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)
 
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
 
Bob Phillips said:
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)
Click to expand...

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)
 
Bob Phillips said:
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

--
Click to expand...

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
 
Back
Top