jmfox
04-09-2013, 02:14 PM
Hi everyone,
I am wondering whether you could help me.We use a spreadsheet to track all of the events we organise.
In one of the cells per row is the list of workers, each of these are then colour coded based on different criteria (I know this is not the most effective way of doing so).
I am lookling at a way of searching for specific names (stored in a string) and them chaning them to black. For example, when adding another name to the sheet I have managed to do this using the following code:
Range("L" & ActiveCell.Row).Characters(InStr(1, Range("L" & ActiveCell.Row), AmbContacts), Len(AmbContacts)).Font.ColorIndex = vbBlack
This does work OK however if there are multiple colours before the string (it changes some of these, it's a bit difficult to explain so should make sense with the example below)
Worker 1, Worker 2, Worker 3 (This works fine with just one blue colour before the additon of worker 3)
Worker 1, Worker 2, Worker 3 (If 3 colours are used, worker 1 also becomes blue)For the life of me I cannot understand why this occurs. If it is of any use, the string "AmbContacts" will always be at the end of the cell.
I really appreciate your advice in advance.
Jack
I am wondering whether you could help me.We use a spreadsheet to track all of the events we organise.
In one of the cells per row is the list of workers, each of these are then colour coded based on different criteria (I know this is not the most effective way of doing so).
I am lookling at a way of searching for specific names (stored in a string) and them chaning them to black. For example, when adding another name to the sheet I have managed to do this using the following code:
Range("L" & ActiveCell.Row).Characters(InStr(1, Range("L" & ActiveCell.Row), AmbContacts), Len(AmbContacts)).Font.ColorIndex = vbBlack
This does work OK however if there are multiple colours before the string (it changes some of these, it's a bit difficult to explain so should make sense with the example below)
Worker 1, Worker 2, Worker 3 (This works fine with just one blue colour before the additon of worker 3)
Worker 1, Worker 2, Worker 3 (If 3 colours are used, worker 1 also becomes blue)For the life of me I cannot understand why this occurs. If it is of any use, the string "AmbContacts" will always be at the end of the cell.
I really appreciate your advice in advance.
Jack