Changing colour cell depending ona selection

Microsoft Office 2007 home and student
December 15, 2009 at 06:06:30
Specs: Windows XP
I have spreadsheet which is based on nutrition, and the user is required to put in their weight in either Imperial or metric. If Imperial is selected there is two spaces in weight to enter there weight in stones and pounds. Is there anyway for when metric is selected that there will be only one white box for kilograms and the box that was white for pounds to go blue like the rest of the spreadsheet.

See More: Changing colour cell depending ona selection

Report •

December 15, 2009 at 08:56:32

Try this:
In cells E3 and E4 enter Imperial and Metric
This will be the data for an in-cell drop-down list that users will choose from
In cell B2 put something like 'Select Imperial or Metric'
Cell B3 will be the selection cell. Click on cell B3.
From the ribbon select Data - Then Data Validation from the data tools group and Data Validation from the drop-down list
In the dialog box that opens, use the settings tab and under Allow, select List and use the Source box to select E2:E3
Click OK
B2 will now have a drop-down with Imperial and Metric and will only accept one of those two options.

Data entry will be in cells B4 and B5

B5 will only be white if Imperial is selected, else it will be blue.
Format B5 as Blue - the same as the rest of the sheet
Click on B5 and select Home from the Ribbon and select Conditional Formatting from the Styles group.
From the conditional formatting drop-down select New Rule and select 'Use a formula to determine which cells to format'
In the lower half in the Format values where this formula is true box, enter

=if(B3="Imperial", TRUE,FALSE)

Then use the format button and select a white Fill for the cell.
Click OK
Cell B5 will now be Blue unless Imperial is selected in B3.


Report •
Related Solutions

Ask Question