Hi,

I have a text box in a form that will contain one of three possible options:
1) blank
2) "Go"
3) "Stop"

I am happy enough with the look of the box as I have set it up when it is
blank but is it possible to conditionally format it so that the box
background goes red when "Stop" is the value and green when "go" is the value?

TIA

Dave

1. Select the cell (or cells if there are more than one)

2. Click Format/Conditional Formatting from Excel's menu bar

3. Click the Add button so that 2 Conditions sections appear

4. Select "Formula Is" from the drop down box for both conditions

5. Put this formula in the empty field for Condition1... =A1="Go"
Click the Format button and choose a green color from Patterns tab

6. Put this formula in the empty field for Condition2... =A1="Stop"
Click the Format button and choose a red color from Patterns tab

Okay your way back to the worksheet.

NOTE: I used A1 for the cell reference, but you should use the address of
the selected cell (or the active cell address if more than one cell is
selected) in place of the A1 in both formulas.

--
Rick (MVP - Excel)


"Risky Dave" <(E-Mail Removed)> wrote in message
news:A5D30ADE-30CE-4EC4-9FF4-(E-Mail Removed)...
> Hi,
>
> I have a text box in a form that will contain one of three possible
> options:
> 1) blank
> 2) "Go"
> 3) "Stop"
>
> I am happy enough with the look of the box as I have set it up when it is
> blank but is it possible to conditionally format it so that the box
> background goes red when "Stop" is the value and green when "go" is the
> value?
>
> TIA
>
> Dave

Rick,

Thanks for the response but I need to do this in a text box on a form using
VB.

Dave

"Rick Rothstein" wrote:

> 1. Select the cell (or cells if there are more than one)
>
> 2. Click Format/Conditional Formatting from Excel's menu bar
>
> 3. Click the Add button so that 2 Conditions sections appear
>
> 4. Select "Formula Is" from the drop down box for both conditions
>
> 5. Put this formula in the empty field for Condition1... =A1="Go"
> Click the Format button and choose a green color from Patterns tab
>
> 6. Put this formula in the empty field for Condition2... =A1="Stop"
> Click the Format button and choose a red color from Patterns tab
>
> Okay your way back to the worksheet.
>
> NOTE: I used A1 for the cell reference, but you should use the address of
> the selected cell (or the active cell address if more than one cell is
> selected) in place of the A1 in both formulas.
>
> --
> Rick (MVP - Excel)
>
>
> "Risky Dave" <(E-Mail Removed)> wrote in message
> news:A5D30ADE-30CE-4EC4-9FF4-(E-Mail Removed)...
> > Hi,
> >
> > I have a text box in a form that will contain one of three possible
> > options:
> > 1) blank
> > 2) "Go"
> > 3) "Stop"
> >
> > I am happy enough with the look of the box as I have set it up when it is
> > blank but is it possible to conditionally format it so that the box
> > background goes red when "Stop" is the value and green when "go" is the
> > value?
> >
> > TIA
> >
> > Dave
>
>

Sorry, I didn't read your question carefully (my fault). Give this TextBox
Change event code a try...

Private Sub TextBox1_Change()
Select Case UCase(TextBox1.Text)
Case "GO"
TextBox1.BackColor = vbGreen
Case "STOP"
TextBox1.BackColor = vbRed
Case Else
TextBox1.BackColor = vbWhite
End Select
End Sub

--
Rick (MVP - Excel)


"Risky Dave" <(E-Mail Removed)> wrote in message
news:190046EE-6668-4A7C-81D3-(E-Mail Removed)...
> Rick,
>
> Thanks for the response but I need to do this in a text box on a form
> using
> VB.
>
> Dave
>
> "Rick Rothstein" wrote:
>
>> 1. Select the cell (or cells if there are more than one)
>>
>> 2. Click Format/Conditional Formatting from Excel's menu bar
>>
>> 3. Click the Add button so that 2 Conditions sections appear
>>
>> 4. Select "Formula Is" from the drop down box for both conditions
>>
>> 5. Put this formula in the empty field for Condition1... =A1="Go"
>> Click the Format button and choose a green color from Patterns tab
>>
>> 6. Put this formula in the empty field for Condition2... =A1="Stop"
>> Click the Format button and choose a red color from Patterns tab
>>
>> Okay your way back to the worksheet.
>>
>> NOTE: I used A1 for the cell reference, but you should use the address of
>> the selected cell (or the active cell address if more than one cell is
>> selected) in place of the A1 in both formulas.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Risky Dave" <(E-Mail Removed)> wrote in message
>> news:A5D30ADE-30CE-4EC4-9FF4-(E-Mail Removed)...
>> > Hi,
>> >
>> > I have a text box in a form that will contain one of three possible
>> > options:
>> > 1) blank
>> > 2) "Go"
>> > 3) "Stop"
>> >
>> > I am happy enough with the look of the box as I have set it up when it
>> > is
>> > blank but is it possible to conditionally format it so that the box
>> > background goes red when "Stop" is the value and green when "go" is the
>> > value?
>> >
>> > TIA
>> >
>> > Dave
>>
>>

Risk,

My thanks

"Rick Rothstein" wrote:

> Sorry, I didn't read your question carefully (my fault). Give this TextBox
> Change event code a try...
>
> Private Sub TextBox1_Change()
> Select Case UCase(TextBox1.Text)
> Case "GO"
> TextBox1.BackColor = vbGreen
> Case "STOP"
> TextBox1.BackColor = vbRed
> Case Else
> TextBox1.BackColor = vbWhite
> End Select
> End Sub
>
> --
> Rick (MVP - Excel)
>
>
> "Risky Dave" <(E-Mail Removed)> wrote in message
> news:190046EE-6668-4A7C-81D3-(E-Mail Removed)...
> > Rick,
> >
> > Thanks for the response but I need to do this in a text box on a form
> > using
> > VB.
> >
> > Dave
> >
> > "Rick Rothstein" wrote:
> >
> >> 1. Select the cell (or cells if there are more than one)
> >>
> >> 2. Click Format/Conditional Formatting from Excel's menu bar
> >>
> >> 3. Click the Add button so that 2 Conditions sections appear
> >>
> >> 4. Select "Formula Is" from the drop down box for both conditions
> >>
> >> 5. Put this formula in the empty field for Condition1... =A1="Go"
> >> Click the Format button and choose a green color from Patterns tab
> >>
> >> 6. Put this formula in the empty field for Condition2... =A1="Stop"
> >> Click the Format button and choose a red color from Patterns tab
> >>
> >> Okay your way back to the worksheet.
> >>
> >> NOTE: I used A1 for the cell reference, but you should use the address of
> >> the selected cell (or the active cell address if more than one cell is
> >> selected) in place of the A1 in both formulas.
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "Risky Dave" <(E-Mail Removed)> wrote in message
> >> news:A5D30ADE-30CE-4EC4-9FF4-(E-Mail Removed)...
> >> > Hi,
> >> >
> >> > I have a text box in a form that will contain one of three possible
> >> > options:
> >> > 1) blank
> >> > 2) "Go"
> >> > 3) "Stop"
> >> >
> >> > I am happy enough with the look of the box as I have set it up when it
> >> > is
> >> > blank but is it possible to conditionally format it so that the box
> >> > background goes red when "Stop" is the value and green when "go" is the
> >> > value?
> >> >
> >> > TIA
> >> >
> >> > Dave
> >>
> >>
>
>