0

I'm trying to make a spreadsheet with tables, with some automation, that keeps track of my work hours. (Note I'm not using the Excel "format as table" feature. This is all by hand). Anyways its set up so I can copy and paste this table as needed. The first column, when you change the date on a new table, all the other dates and day of week lables update automatically.

The problem I'm having is I'm trying to use conditional formatting to change the column color to red when the coumn is a weekend, i.e. "SAT" or "SUN".

You can see I've only implimented it in the first few rows. The problem I'm having, is when I drag the formatting rules from the first column, to apply to all columns it works to change the "SAT" and "SUN" columns, but on the days that aren't weekends it "whiting out" only the bluish/aqua colored formated cells, but not the gray cells. Experimenting it seems to leave the gray since I've formated the whole row.

So how do I keep the conditional formating from "whiting out" the bluish/aqua cells?

Here's the rule that I'm using by the way..

 =OR(B4="SUN",B4="SAT")

I added that rule to individually to B5, B6, B7, and B8, then dragged it accross to column Q.

Improve this question

1 Answer 1

Reset to default
1

The problem is that when you drag a cell across, you drag its formatting as well. So, if you set up the rule as follows, you won't need to drag the cell.

You need to create just one rule for this. Not a separate rule for every row that is dragged across.

The formula you have for the rule that you create in the top-left cell of the range you want to apply the rule to should be changed to this:

=OR(B$4="SUN",B$4="SAT")

This will ensure that when you change the "Applies to" range to include more rows, it will always reference row 4. But when you change the "Applies to" range to include more columns, the column reference in the rule will change appropriately.

After you have created the above rule in cell B5, go to Conditional Formatting>Manage Rules and change the "Applies to" range to be the entire table:

enter image description here

Improve this answer
1
  • Your explaination for what the cause was allowed me to fix the problem! However I can't use your solution of using the formula " =OR(B$4="SUN",B$4="SAT") " becasue I'm gong to be cutting-and-pasting this table all over the worksheet. But your explaination helped me fix the problem rather quickly. Thank!
    – Frank
    Commented Aug 27, 2020 at 14:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .