Forum Discussion
Autoformat cell background fill based on cell background fill above
Hi, I've created a holiday calendar for team members, using a red background fill to indicate people being away. Now I'd like to add a summary row, showing the available time with a green background fill, when nobody is on holiday - so as to easily see available times for scheduling meetings.
A possible rule might look like this: Cell C26 = green fill if cells C1 to C25 contain no fill. Cell C26 = no fill if cells C1 to C25 contain (one or more) cells with background fill. This rule would then be applied to all cells of row 26. Any suggestions?
Select C1:Z25 or as far to the right as needed.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'not equal to' from the second drop down.
In the box next to it, enter the formula=""
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.Next, select C26:Z26 or as far to the right as needed. C26 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=COUNTA(C1:C25)=0
Click Format...
Activate the Fill tab.
Select green as fill color.
Click OK, then click OK again.
Do you color the cells in rows 1 to 25 manually? If so, there is no easy way to do what you want.
Would it be feasible to enter something in cells to indicate absence, for example an "x" or an "A"?
If so, you could create conditional formatting rules to
a) color the cell itself red, and
b) color the cell in row 26 the way you want.
HansVogelaar Many thanks; yes, I have been colouring them manually - but I could also use an x instead, or a . (period).
Select C1:Z25 or as far to the right as needed.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'not equal to' from the second drop down.
In the box next to it, enter the formula=""
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.Next, select C26:Z26 or as far to the right as needed. C26 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=COUNTA(C1:C25)=0
Click Format...
Activate the Fill tab.
Select green as fill color.
Click OK, then click OK again.
