Converting color coded cells into numbers (Excel 2007, Windows Vista)

[angExcel-entflea]

Greetings-

I am a Windows Vista user using Excel 2007. I have recently received a large Excel dataset containing colors that need to be converted to numbers, e.g. red cells should be 1, blue cells should be 2, etc. I am seeking a way to "find and replace" the color data with numbers efficiently.

Thank you in advance, all suggestions are greatly appreciated. If my post is not in the right section, kindly tell me where to post this concern of mine.

[maniacb]

We can assist better if you share a sample file as per the yellow banner above.

[angExcel-entflea]

I'm only showing part of it - can't show it all, as per our company policy regarding company-related files.
But at least you'll get a clear picture of what is asked...

[maniacb]

What do the numbers in the colored cells signify? Don't you want all the same color the same value, like all red = to 1?

[jindon]

angExcel-entflea,

Try

Please Login or Register  to view this content.

[angExcel-entflea]

@maniacb
What do the numbers in the colored cells signify? Don't you want all the same color the same value, like all red = to 1?

Yes, it is - regardless whether there are any values inside the cells.
If there are red-colored cells, they must be all be numbered "1"; if blue, they'll be numbered as "2" (and so on and so forth...)

That is what the dataset looked like when it was sent to me.
I tried looking at the conditional formatting feature of my Excel 2007 but there isn't what I was looking for. Except for coloring the background color of a range of cells when the values inside correspond to a certain numerical range, there's nothing else...

Really, I'm very much stumped :p

[angExcel-entflea]

@jindon
Geez - that is VBscript, right?
I'll give it a shot...

By the way, how different is Visual Basic from VBscript? Aside from the key difference that VBscript tackles Excel cells, are the syntaxes of both programming languages very much alike?

[angExcel-entflea]

angExcel-entflea,

Try

Please Login or Register  to view this content.

If I understand this script, there's a portion here that - what seems to me - will display pop-up a dialog box, telling me I need to select a color for certain range (for example, all cells colored "red").

If I do that, the rest of script automatically converts ALL the cells colored red into cells containing the value "1" inside them (regardless of the previous values inside each) - is my understanding of that correct?

[jindon]

@jindon
Geez - that is VBscript, right?
I'll give it a shot...

No, It's a VBA(Visual Basic for Applications) code.

By the way, how different is Visual Basic from VBscript? Aside from the key difference that VBscript tackles Excel cells, are the syntaxes of both programming languages very much alike?

VBA (Visula Basic For Applications);
Languages ​​that works in Microsoft Office products such as Access, Excel, and Outlook.