Function help

[Carolj]

This is my first post here because I have a problem that I cannot solve. I
don't have a lot of experience with Excel so this may be a simple fix, so
here goes--

I have a worksheet that has tasks on it and what I would like to do is add a
column (which will be column J) and in that column, what I want to be able to
do is go in and type just an I for In Progress (Yellow), C for Completed
(Green) and NA for Not Applicable (Red). I am not sure if it can be done
but I think I would like to type the "I" and have In Progress come up in
yellow and so forth. I believe it is an IF statement but I have been working
on this for days now so I think I need a little help to get through this one.

If there is an easier way to do this, please let me know.

Thanks so much in advance for any help you can give.

 

[Guest]

If you are only using three conditions, you can do this using conditional
formating...go to FORMAT > CONDITIONAL FORMAT and enter your criteria
there...hope this helps!

 

[Gord Dibben]

Format>Conditional Formatting.

Condition 1: Cell Value is: equal to "I"

Format to Pattern>Yellow

Click on "Add" and Condition 2.............

As above but "C" and Green

"Add" again and Condition 3


Gord Dibben MS Excel MVP

 

[Sandy Mann]

but I think I would like to type the "I" and have In Progress come up in
yellow and so forth. I believe it is an IF statement but I have been
working
on this for days now so I think I need a little help to get through this
one.

You cant have a formula in the cell in which you want to add to letter
because entering the letter will overwrite the formula.

To do what you want try:

Right-click on the sheet tab and select "View Code"

then copy and paste this code into the sheet module that will appear:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 10 Then Exit Sub

Application.EnableEvents = False
Select Case UCase(Target)
Case Is = "I"
Target.Value = "In Progress"
Target.Interior.ColorIndex = 6
Case Is = "C"
Target.Value = "Completed"
Target.Interior.ColorIndex = 4
Case Is = "N"
Target.Value = "Not Applicable"
Target.Interior.ColorIndex = 3
End Select
Application.EnableEvents = True
End Sub

Note that you only need N enetered to get "Not applicable"
All other entries in column "J" will be entered unchanged.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Carolj]

Thanks. It worked great.

Format>Conditional Formatting.

Condition 1: Cell Value is: equal to "I"

Format to Pattern>Yellow

Click on "Add" and Condition 2.............

As above but "C" and Green

"Add" again and Condition 3

Gord Dibben MS Excel MVP

This is my first post here because I have a problem that I cannot solve. I
don't have a lot of experience with Excel so this may be a simple fix, so

[quoted text clipped - 11 lines]

Thanks so much in advance for any help you can give.

 

[Gord Dibben]

Thanks for the feedback.

Gord

Thanks. It worked great.

Format>Conditional Formatting.

Condition 1: Cell Value is: equal to "I"

Format to Pattern>Yellow

Click on "Add" and Condition 2.............

As above but "C" and Green

"Add" again and Condition 3

Gord Dibben MS Excel MVP

This is my first post here because I have a problem that I cannot solve. I
don't have a lot of experience with Excel so this may be a simple fix, so

[quoted text clipped - 11 lines]

Thanks so much in advance for any help you can give.

Gord Dibben MS Excel MVP