Changing Cell Colour

[bubba06]

Hi - I have a drop down box with 3 entries in it - 1. Not Started 2. Work In Progress 3. Completed - if i selected "not started" I want that same cell to change to red with "not started" - Orange for "work in progress" and green for "completed" - and if completed is selected i need to make the next cell come up with date (now)

Thanks in Advance

 

[jmthompson]

The first part can be completed easily with conditional formatting, I believe the second requires a macro. Are you okay with using macros?

 

[bubba06]

Yeah im kinda familiar with macros

 

[jmthompson]

So there are 2 approaches, you can have a macro to run on demand, possible with a button that would enter the date if the drop-down says "completed" or you could go with an event code.

Event codes can get tricky because you have to allow for some human error. If someone marks something complete today, the date is recorded. What happens if next week, or month they change the status:
If intentionally- do you want the date retained, what if it was a mistake and they change it back, the original date is gone.

You could do an event code include a msgbox asking the user to confirm their drop-down selection...

 

[bubba06]

Sounds a little hectic huh - i thought it would be quite basic but it has me scratching my head - i played around with Conditional Formatting but cant seem to get it right with selection and color etc

 

[jmthompson]

For the conditional formatting. you would set three individual rules on the cell/column

= "Not Started" and choose your formatting

= "Work in Progress" and choose your formatting

= "Completed" and choose your formatting

 

[bubba06]

Thanks i got that bit done - now all i have to do is figure out how i can put date (now) into the next cell when selecting "complete" any suggestions?

 

[jmthompson]

know which approach you'd like to do?

 

[bubba06]

Which way do u suggest?

 

[jmthompson]

Are there a lot of users updating this sheet or just a few.

How well do you trust them?