Assign a value depending on the font color

Solved/Closed
Rishi - Mar 15, 2011 at 10:45 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Mar 17, 2011 at 10:32 AM
Hello,

Can someone help me with this.

I have a column with some text in each cell of that column.

The text font is in either red or black color.

I need to sort it so that I have all black colored text on top and then all the red color texts.

Im thinking if I can somehow assign a value to the color of the font, I should be ok.

Problem is I dont know how.


Can someone tell me how to.

Regards

Rishi

1 reply

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Mar 15, 2011 at 11:33 AM
Hi Rishi,

Make sure you save your file before playing with macro's, since they can't be undone using the blue arrows!

This code will put a 1 next to the cells where the text color is red:

Sub test()
Set MR = Range("A1:A10")
For Each cell In MR
If cell.Font.ColorIndex = 3 Then cell.Offset(0, 1) = 1
    Next
End Sub

Change Range("A1:A10") to suit your range.

Offset(0,1) means move 0 rows down and 1 column to the right. Change this so it doesn't mess up your data.

Best regards,
Trowa
0
Thak you Trowa.

That worked.

I was wondering if the Font color is different in different cells, can we assign a specific number to each cell.
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Mar 17, 2011 at 10:32 AM
Hi Rishi,

You mean that if, for example, a text color is green then assign a 2, text color is yellow assign a 3 etc...
Your first step is to figure out the Font.ColorIndex value. Do this by recording a macro and then changing the text color to the colors you want to use. Stop recording and look at the generated code to find the values.
Then add as many lines of code as you need.
Your code could then look like:
Sub test()
Set MR = Range("A1:A10")
For Each cell In MR
If cell.Font.ColorIndex = 3 Then cell.Offset(0, 1) = 1
If cell.Font.ColorIndex = 5 Then cell.Offset(0, 1) = 2
If cell.Font.ColorIndex = 8 Then cell.Offset(0, 1) = 3
    Next
End Sub

Is this what you had in mind?

Best regards,
Trowa
0