0

Current

I want to extend the formatting to the next cell (which contains mins) as well.
Here is the formula I'm using:
Formula P: Formula S:
Both of them are applied to =$A$1:$B$1,$36:$50,$A$2:$C$35,$E$2:$XFD$35,$D$1:$XFD$1

Um9vdAo
  • 53
  • 2
  • 9
  • i assume you mean so that it fills nicely across both cells (left of cell one is blue, and the right of cell 2 is red). I don't think that's possible, but maybe someone will find an answer – Hooded 0ne Dec 20 '20 at 23:07
  • if you want to just format column c based on the values in column b, you would just add a new rule use b1 in your formula and then c:c in the applies to – Hooded 0ne Dec 20 '20 at 23:10
  • Add `$` in front of `A1`: `=ISNUMBER(SEARCH("P:",$A1))`. You can than extend the range to whatever column you like to be formatted like this. The `$` locks the position of the column in the formula. – P.b Dec 21 '20 at 06:27

1 Answers1

1

Strictly speaking, it's not possible to distribute a gradient background across 2 cells. There are a couple of possible workarounds if you can rearrange your sheet a little:

Option 1: Concatenate the 2nd and 3rd columns into a single cell and apply formatting to that instead.

Option 2: Insert a blank column between them and make them roughly equal widths. Then create conditional formatting rules on each of the columns.

enter image description here

Steps for Option 2:

  • Insert blank column, select the rule column and the new column, then change Horizontal Alignment to Center Across Selection: enter image description here

  • Select the rule column and apply conditional formatting for a solid fill: enter image description here

  • Select the minutes column and apply conditional formatting for a solid fill: enter image description here

  • Select the blank column and apply conditional formatting for the gradient: enter image description here

  • Repeat for the other condition

Note that when applying relative conditional formatting formulas, it is based on the Active Cell within the selection. So in my screenshots, I made each selection by click-dragging starting from the top cell down, and then referenced the first adjacent top cell in the formulas.

Michael
  • 4,563
  • 2
  • 11
  • 25