An interesting one for all you experts...
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 |
An interesting one for all you experts...
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 |
An interesting one for all you experts...
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 |
An interesting one for all you experts...
"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 |
An interesting one for all you experts...
Is there a list of all the color indexes somwhere ... just so I can pick up
all the colors easily? Thanks again Andy |
An interesting one for all you experts...
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 |
An interesting one for all you experts...
"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 |
An interesting one for all you experts...
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 |
All times are GMT +1. The time now is 06:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com