Conditional Formatting following a drop down box entry

Hi all,

(Using Office 2010)

I’m fairly new to using Excel, and I’m managing on the day-to-day things, but every now and again, I want to do something that makes things a little easier for management to understand.

Members of management like colours and things.

I’ve made a sales spread sheet and a drop down box in the “Status” column so I can filter the results.

The drop down selections are in the “N” column.

Now, management would like to have the row change colour depending on the “Status” selected from the drop sown box.

For example:
if the status is any of the “Lost”, I would like the row to turn RED;
if the status is any of the “Won”, I would like the row to turn BLUE;
if the status is “Ongoing – 50/50 Interest”, I would like the row to turn GREEN;
if the status is any of the other “Ongoing”, I would like the row to turn YELLOW.

I know it’s something to do with the Conditional Formatting function but do not know how to get it to do this.

I’ve attached the spread sheet, as I’m not the greatest at explaining things.

A solution would be grand, and a brief explanation so I can do it in future would be great.

Thanks!

Hi

I have added some conditional formatting to the cells in this copy of your sheet, which will give the colours you want based on the conditions you stipulated. essentially, it uses 4 lines of connditional format depending on the text in the cell in column N based on this formula:

=LEFT($N2,8)="Interest"

I have replaced the names of what appear to be real staff members and clients in your original file. If these are indeed real names, you might want to consider whether this information should be posted on this website. you can make up a dummy sheet or replace names when posting example spreadsheets to avoid uploading identifying information. If you want the sheet removed, contact one of the moderators and they will be able to delete it for you.

All the best

Nicky

Hi Nicky,

Many thanks for your help, but it doesn't seem to work :-(

I managed to get the single cell in the "Status" column to change colour, but I'd like the entire row cell colour to change.

I've attached what I've now done, but would like the entire row to change colour.

Any more help would be great.

Thanks,

Tallon

Hi,

Is the attached spreadsheet what you are looking for?

There are four conditional formatting rules here, the first identifies the 'Lost' people and puts them in Red, the second identifies the 'Won' in Blue.

The third rule identifies the 'Ongoing - 50/50 interest' and puts these in Green, but there's a Stop if True condition on this so that the final rule can identify the remaining rows with an 'Ongoing' status and put them in Yellow.

Each rule uses $I2. If you didn't have the $ here, when column B is being evaluated it would look at column J for a decision (since you began in column A using column I, it moves on in this manner unless you put a $ in to fix it on I).

Since there's no $ for the column, the conditional formatting formula applies down each row as far as you wish to apply the rule... just extend the range to cover whatever size you like.

I'd also change the colour yellow to something else, it's always impossible to read on a white background if you're distributing this to people.

Hi Guys,

Thanks for all your help! I'm nearly there (with a little tweeking!).

I have another sheet that I want to do a similar thing to, if you could have a look?

As the previous, I'd like the row to fill the corresponding colour following the selection from the "status" field.

Then I'm done!

Thanks!

This should do it! Remember if you want to do this for an increased number of rows (past row 300 on mine) you'll need to increase the range of cells that the conditional formats apply to.