Visual basic to step through range and store a value based on text attribute value.
 

My end objective is to be able to sort the database that has records in
many
different colors by the color of the text.

To group by the color or sort by the names of the colors? I don't know that
the Font.ColorIndex implies a specific color, only an index into a palette.
The ColorIndex would be sufficient for sorting, it seems to me, although you
could assign names to the indicies.

This will put the ColorIndex of a target cell into a cell offset two to the
right of the target cell for each cell in the selection.

Sub Main()
Dim cell As Range
For Each cell In Selection
cell.Offset(0, 2).Value = cell.Font.ColorIndex
Next cell
End Sub

--
Bob Kilmer


"Ashleigh K." wrote in message
...
Help, my boss is making me do a time wasting excercise and I can't figure
it
out.

I'm trying to sort a HUGE excel database consisting of roughly 22,000
records. What I'd like to do is to create a visual basic routine that
will
step through each record in the database and store a value in a free cell
indicating the text attributes of the value in column A. The database
looks
something like this:

Column A = First Names
Column B = Last Names
Column C = Currently blank, but I want it to store the value of the visual
basic routine.


So, a pseudocode example would do something like this.

Start
For 1 to x (x=total number of records 21,000)
If font color attribute for Cell A(x) = Red
Then Store the word "Red" in cell C(x)
endif
next x
end

I know from a macro that I tried to record that it will use something like
Selection.Font.ColorIndex = 3
but I don't know how to make it step through each cell and then store the
new value in the corresponding row in column C.

Does this make sense? Am I approaching this the right way. Does anyone
have a suggestion on how I might write the visual basic to achieve this.
My
end objective is to be able to sort the database that has records in many
different colors by the color of the text. I think this approach (if I
can
figure it out) will get me there.

Thanks for all of your help!

A.