Does anyone know how to do this in excel?
I need to do the following:
I have a large list that is categorized by font color (already color coded). I need the cells adjacent to the list to be automatically categorized based on the color of the cell next to it. For example, lets say my first three rows are:
Row 1, Column 1 (in red font)
Row 2, Column 1 (in blue font)
Row 3, Column 1 (in green font)
etc.
I need the cells in column 2 to say something based on the font color of the list in the first column. For example, the red color font will mean "x" in column 2, green color font means "y", etc.
Anyone know how to do this without doing it manually?
Maybe conditional formatting can do it
this is a job for VBA (at least, i can't think of a way to do it otherwise).
Sub colorCode():
Dim selRange as Range
set selRange = Application.InputBox(type:= 8)
'note, the above allows you to select the range you want to loop through. you could always hardcode the range though
selRange.select
for each cell in selRange if cell.interiorcolor= *add the color here - also i forget if it is cell.interiorcolor or something else then blah blah blah
do some more if statements for your other colors
end if
next cell
end for end sub
That is a seriously rudimentary first attempt at what you need to input, but I'm pretty sure you could google the answer or at least google the missing parts of what I just wrote.;
Anway, good luck
It's not completely automatic but it beats updating it manually - you can filter by colour.
Use the basic filter tool (ctrl shift L) and once you do that, you can "filter by colour", and "font colour" should be a section you can select.
Once you isolate the colour, you can paste in the appropiate value you want to id it in the column beside it.
VBA is not necessary. This can be done with an excel formula.
1.Open the Name Manager 2.Give the formula a name. Say FontColor 3.Type this formula in Refers To =GET.CELL(24,OFFSET(INDIRECT("RC",FALSE),0,-1)) and click OK
Now go to the second column of your worksheet and simply type the formula =fontcolor
The respective number corresponding to the font in the adjacent cell will be displayed. You can then use a vlookup or any other function from there to define certain font colors as desired labels (X, Y, etc). I have a simple example in excel I can send you but not sure I can attach it to a forum comment? I will try a PM.
Illum dolorum quia nostrum molestiae. Veritatis fugiat voluptatum reiciendis. Officiis sed ipsum in ut ut velit. Eveniet quae nemo eveniet dolores similique ipsum error.
Libero accusantium quasi repellat qui nihil. Blanditiis laboriosam aliquam quaerat sed. Est explicabo tempora in deserunt cumque quos. In est nisi harum provident excepturi est.
Reiciendis iusto dolore qui est enim. Quaerat quo dolore omnis et tenetur corrupti iusto. Itaque eum consequatur quia magni. Neque blanditiis aspernatur dicta est in odio. Aspernatur omnis tempore rerum.
Eos est repudiandae amet sapiente. Error quod vitae impedit velit repellat.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...