How to Conditionally Format a Cell Based on the Presence or Absence of Data in a Range of Other Cells


In the screenshot above, when a value is entered under the time slots on the right (causing them to change format to a Deep Red Fill with Bold White Font), the Activity in the far Left Column darkens a shade and turns the font bold to let me easily review the activities which have been engaged in throughout the day.

  • Select the Cell you want to change formatting for based on the presence of data in other cells
  • Go to Home > Conditional Formatting > New Rule… > Use a Formula to determine which cells to format
  • Paste the following:
    • =AND(LEN($[cell1]), OR(COLUMN()<3, AND(COLUMN()>4, COUNTBLANK([cell1]))), COUNTA($[cell2]:$[cell10]))
  • Where [cell1] is the cell you want to change, [cell2] is the beginning of the range you want to monitor for data, and [cell10] is the end of the range. You’ll replace [cellX] with your cell letter and number, just like B6 without any square brackets used.
  • Click Format & Format to a Darker Fill Color & Bold Font etc
  • Hit OK

And if you want the opposite, for the cell to change color if anything is Blank which shouldn’t be, use this formula:

=AND(LEN($[cell1]), OR(COLUMN()<3, AND(COLUMN()>4, COUNTBLANK([cell1]))), COUNTBLANK($[cell2]:$[cell10]))

Note the difference is COUNTBLANK, vs COUNTA in the first one.

And to apply it to a range of cells, Just click on the cell you just did and go to Conditional Formatting > Manage Rules > Applies to > and change it from $B$6 to $B$6:$B$50 for the range of cells, or $B$6:$B$50,$B$56:$B$87 for two ranges of cells divided by some other cells in between, etc

This also works with the normal Conditional Formatting, so that the cells that are added change color, then the header is also highlighted in a different color showing something on that row has been entered.

That’s done by Conditional Formatting > New > Format only cells that contain > Cell Value > Between > .001 and 10000000

– – – – – – –

And if you are interested in making the number formatted to 2 decimal places (.25, .33, 2.75 etc) unless the second decimal place is a zero then show only one decimal place (.5 etc), and if there are no decimal places just show a single decimal place zero (3.0 etc), do the following:

Format > Number > Custom > and enter: 0.0####

and if you want to limit lengthy decimal places to just two, type 0.0#

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s