Assign a value depending on the font color
Solved/Closed
Rishi
-
Mar 15, 2011 at 10:45 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 17, 2011 at 10:32 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 17, 2011 at 10:32 AM
Related:
- Assign a value depending on the font color
- Html font color - Guide
- Notepad++ background color - Guide
- Rg45 color coding - Guide
- Sound card color code - Guide
- Powertoys color picker download - Download - Other
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Mar 15, 2011 at 11:33 AM
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:
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
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
Mar 15, 2011 at 01:51 PM
That worked.
I was wondering if the Font color is different in different cells, can we assign a specific number to each cell.
Mar 17, 2011 at 10:32 AM
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 SubIs this what you had in mind?
Best regards,
Trowa