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

Hi Ashleigh,

For i = 1 To 21000
Select Case Cells(i,1).Font.Colorindex
Case 3 : Cells(i,3).Value = "Red"
Case 10 : Cells(i,3).Value = "Green"
End Select
Next i

Extend with extra case statements and values
--

HTH

Bob Phillips

"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.

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

Thanks Bob and Bob....your solutions worked great, I really appreciate your
help!

Ashleigh
"Bob Phillips" wrote in message
...
Hi Ashleigh,

For i = 1 To 21000
Select Case Cells(i,1).Font.Colorindex
Case 3 : Cells(i,3).Value = "Red"
Case 10 : Cells(i,3).Value = "Green"
End Select
Next i

Extend with extra case statements and values
--

HTH

Bob Phillips

"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.