Convert colored filled cells to numbers

[kevinduque]

Hi Team,

so i have tables below and I want assigned cells filled with color red, yellow, and white converted to corresponding numbers. any idea or help is very  muchappreciaTED

Red	1
Yellow	2
White	3

[#2]

Hi @kevinduque 

Please watch attached video and tell me if this is what you're looking for

[#3]

cant we do this with a formula>>??

like replace. @PascalKTeam 

[#4]

My solutions uses a few lines of VBA code since I don't think this can be done without VBA

[#5]

@PascalKTeam - can you share please the VB code would really appreciate it

[#6]

Hi @kevinduque 

Yes of course I can. Please see attached file

[#7]

Can you do a code wherein when I paste a data in column A and on Column B it will automatically indicate the corresponding numbers?

[#8]

Hi @kevinduque 

Sure can

This version runs the macro every you paste something

[#9]

THis is Great - so from the formula where can I change if id like to paste the colors on column A and display the results in column G or any column id like to put the result?

[#10]

@kevinduque

Open the VBA editor with Alt+F11

The VBA code is in Sheet 1

The column with the color is defined here:

If Cells(x, 1).Interior.ColorIndex = 3 Then Cells(x, 1).Offset(0, 1) = 1

Becasue A is the first column, there is a 1. If you want to change to column C, just change this number to 3

The column where the number is pasted is defined here

If Cells(x, 1).Interior.ColorIndex = 3 Then Cells(x, 1).Offset(0, 1) = 1

It's defined as an offset of 1 to the right of the colored cell. but you could also offset it by a different number. 

Just play around with it to see what happens when you change these numbers. 

[#11]

last question and thank you you have been very helpful - id like to assign this MACRO to a sheet in my workbook what is the command for that? that is this macro will only run in that particular sheet?

[#12 · Accepted solution]

My pleasure
The steps you have to perform are the following
1. Open my file (the one which contains the macro)
2. Open the VBA editor (Alt+F11)
3. Navigate to sheet1
4. copy the entire code you see
5. Open your file
6. Open the VBA editor (Alt+F11)
7. Select the sheet in which you want the code to run (yes, the code will only run in this sheet)
8. paste the code into the VBA editor
9. test, save and hopefully be happy with it :)

[#13]

@PascalKTeam Thank you so much it works perfectly

[#14]

@PascalKTeam Hi -

I did use the code to diff file woudl oyu know why its not working? theres no error message but for some reason its not working 

[#15]

Have you adjusted the code for your columns as explained above?

[#16]

@PascalKTeam  yes so basically I have two diff file name but the same source both file only have two sheets so the first one just perfectly working but the other one for some reason its not running...

[#17]

I would need more information to find out the reason for the problem...
Can you post the file?

[#18]

@PascalKTeam its all good now thank you i restarfted my laptop and it worked thank you... can you help me with my other qeuries?

[#19]

OK good.
Yes i can but we should not use this topic
You can send a private message to me if you want