Forum Discussion
Solved
Convert colored filled cells to numbers
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 Y...
- 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 🙂
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?
- 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?
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.