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.
Ullam ullam impedit id quo sunt. Reprehenderit aut vel quaerat. Fugit iste fugit vel quisquam et maxime. Soluta et cupiditate perspiciatis.
Quod dolorem facere assumenda aut optio dignissimos molestiae. Facere perferendis non nobis earum est quidem molestiae aperiam. Officia quo voluptas qui ducimus.
Esse impedit impedit hic aut nobis. Dolores est enim ullam voluptates occaecati.
Repellat pariatur deserunt suscipit magni non dolorem. Odit et dicta eligendi officia quos. Possimus sed omnis beatae. Temporibus rerum aliquam velit et. Quaerat voluptatum quia quibusdam saepe ut. Nemo alias commodi est aut.
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...