Format color of fonts in column a based on text choice in column b

[Stryker2013]

I want to have the font color in one column be driven by the value in another cell.

I want the cells in column a to change color based on what the value is in column B

if column b cell b1 is "completed" then column a cell a1 font will be Green

If column b cell b1 is "IP" then column a cell a1 font will be Yellow

If column b cell b1 is "NC" then column a cell a1 font will be Red

If column b cell b1 is "M" then column a cell a1 font will be Purple

I will copy this formula down the sheet so it will be applied to all rows in the sheet.

Any ideas. I was trying if statements but apparently I didn't get it right.

 

[Perpa]

Re: Format colot of fonts in column a based on text choice in column b

Stryker2013,
The following is a macro solution. Place the macro in a standard code module as follows:
Press Alt +F11 to open the VB Editor...Copy the following code (without the C.o.d.e line) into the window that opens. Change the starting row if your data does not start in row 2.
Close the VB Editor and Save the workbook as macro enabled.
Then when you are done placing 'Completed', 'IP', 'NC', or 'M' in columnB, run the 'Font_Color' macro as follows:


Press Alt + F8 and select 'Font_Color' and then 'Run'.
Good Luck.
Perpa

Sub Font_Color()
Dim LR, rw As Long


LR = Cells(Rows.Count, "A").End(xlUp).Row


    For rw = [COLOR=#ff0000]2[/COLOR] To LR     [COLOR=#ff0000]'Change starting row to suit, as show starting row is '2'[/COLOR]
        If Cells(rw, "B") = "Completed" Then Cells(rw, "A").Font.ColorIndex = 10    'Green
        If Cells(rw, "B") = "IP" Then Cells(rw, "A").Font.ColorIndex = 6        'Yellow
        If Cells(rw, "B") = "NC" Then Cells(rw, "A").Font.ColorIndex = 3        'Red
        If Cells(rw, "B") = "M" Then Cells(rw, "A").Font.ColorIndex = 21         'Purple
    Next rw
End Sub

 

[mikerickson]

Re: Format colot of fonts in column a based on text choice in column b

You can do this with Conditional Formatting.
Select cell A1
Set Conditional Formatting with the formula =($B1="completed") to make a green font
Add another Format Condition with the formula =($B1="IP") to make a yellow font.
etc...
Then copy the cell to the rest of column A.