2 colors in formula

JAZZWAKEFOREST

Board Regular
Joined
Dec 1, 2005
Messages
92
I have created the formula below, but I would like to add color to the word GO and STOP. GO will be Green and Stop will be red. How do I do that? HELP

ActiveCell.FormulaR1C1 = "=IF(RC[1]=RC[-1],""GO"",""STOP"")"
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
    With ActiveCell
        .FormulaR1C1 = "=IF(RC[1]=RC[-1],""GO"",""STOP"")"
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""GO"""
        .FormatConditions(1).Font.ColorIndex = 10
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""STOP"""
        .FormatConditions(2).Font.ColorIndex = 3
    End With
 

Babydum

Board Regular
Joined
Feb 24, 2005
Messages
164
You need to use conditional formatting on the cell.

Go to format > conditional formatting.

Choose "Cell value" & "Is equal to" and type red, and set your formatting, then click "add" and do the same for green.

HTH
 

JAZZWAKEFOREST

Board Regular
Joined
Dec 1, 2005
Messages
92
Stupid question... Do I type in the Formula? Or do I type in the word Stop and GO? Do I select the whole column?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

JAZZWAKEFOREST said:
Stupid question... Do I type in the Formula? Or do I type in the word Stop and GO? Do I select the whole column?

Did you try the code I posted? That will do it for you.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

JAZZWAKEFOREST said:
Went into the macro and posted the formula you sent nothing happened.

The code I posted will put a formula in the ActiveCell that returns GO or STOP. If it's GO it will be coloured green. If it's STOP it will be coloured red.

That's what happened when I ran it.
 

JAZZWAKEFOREST

Board Regular
Joined
Dec 1, 2005
Messages
92
Not sure what I am doing wrong, but here is what I have in the macro so far. STOP and GO do come up, but not in the colors.

End With
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]=RC[-1],""GO"",""STOP"")"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I9000"), Type:=xlFillDefault
Range("I2:I9000").Select
Range("I4").Select

With ActiveCell
.FormulaR1C1 = "=IF(RC[1]=RC[-1],""GO"",""STOP"")"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""GO"""
.FormatConditions(1).Font.ColorIndex = 10
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""STOP"""
.FormatConditions(2).Font.ColorIndex = 3
End With
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The code I posted replaced the one line of code you originally posted. It wasn't additional. Replace all the code you have now posted (exclucing the extraneous End With at the beginning) with this:

Code:
    Range("I2").Select
    With ActiveCell
        .FormulaR1C1 = "=IF(RC[1]=RC[-1],""GO"",""STOP"")"
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""GO"""
        .FormatConditions(1).Font.ColorIndex = 10
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""STOP"""
        .FormatConditions(2).Font.ColorIndex = 3
        .AutoFill Destination:=Range("I2:I9000"), Type:=xlFillDefault
    End With
    Range("I4").Select
 

Watch MrExcel Video

Forum statistics

Threads
1,118,795
Messages
5,574,352
Members
412,588
Latest member
FabrizioMaurizio
Top