Forum Discussion

Copper Contributor

Jul 02, 2024

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 ...

Formulas and Functions

HansVogelaar
Jul 02, 2024
t-00-k

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.

HansVogelaar

MVP

Jul 02, 2024

t-00-k

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.

t-00-k
Copper Contributor
Jul 02, 2024
HansVogelaar Many thanks; yes, I have been colouring them manually - but I could also use an x instead, or a . (period).
- HansVogelaar
  MVP
  Jul 02, 2024
  t-00-k
  
  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.
  - t-00-k
    Copper Contributor
    Jul 02, 2024
    Many thanks HansVogelaar, that was very helpful, really appreciated!

Forum Discussion

Autoformat cell background fill based on cell background fill above

Share

Resources