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"")"
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,437
Messages
5,831,620
Members
430,077
Latest member
CoulterM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top