Use Conditional Formatting to Shade Several Cells Based on Value of One Cell

[greenie256]

Project1.jpg

Hi,

I have set up a simple spreadsheet which shows me how many staff I have on at any particular time. I require to shade boxes so I can see how many staff there are so for example if I have 2 staff on, 2 boxes in the column should be shaded. I have been trying to do conditional formatting for this but the only way I can get it working is to format each cell indiviually which is extremely time consuming. Is there a way to paste the conditional format but amend it automatically.

Not sure I have explained this very well so have attached an image of the spreadsheet. The value in row 2 is the number that gets changed and hopefully the shaded cells in the colums will correspond with that number.

Thanks

[NBVC]

I am not sure if your sample is representative of actual expected results... You say that row 2 determines how many cells should be shaded, yet some don't seem to coincide with that rule....

Assuming the sample is wrong and you actually want the cells coloured based on number in row 2.. then select the range from B4 to U and last row...

Go to Home|Conditional Formatting and select New Rule.

Select "use a formula to determine which cells to format" and then enter formula: =ROWS(B$4:B4)<=B$2

Click Format and choose colour from Fill tab.

Click Ok

Click Ok to finish

[greenie256]

Thank you so much, that has done the trick.

Going to save me a lot of time (and errors!!).

Thanks again.