Conditional Formatting a row only for cells to right based on cells to left

[PETTEXCEL]

Hello,

I am familiar with conditional formatting, but this has me perplexed. I have a portfolio table that includes Projects on the Y-axis and Quarters along the X-axis on top. Our project managers enter in the number of hours they invest into each project by quarter.

Goal:

• Enter "complete" in the cell aligned to the Project and Quarter.

• Cell containing "complete" turns gray
• All blank cells to the right (future quarters) turn gray
• Blank cells to the left would not be affected by this conditional formatting, because a project may not have initiated yet.

I have attempted multiple formulas within conditional formatting with no success (Search, If/Then, AND). The Cell Value contains option works for "completed". If I include the Format only blank cells, then cells to the right and left of completed turn gray.

Let me know if I can be more clear.

Thanks.

 

[dreid1011]

Welcome to the board.

Perhaps this combination of two CF rules:

Assuming your table starts in A1 with Y-Axis A2 and down, X-Axis B1 and across(otherwise, change ranges to suit your data):

=B2="completed"
=AND(COUNTIF(B2:$G2,"completed")<1,COUNTIF($B2:$G2,"completed")=1)

 

[PETTEXCEL]

Welcome to the board.

Perhaps this combination of two CF rules:

Assuming your table starts in A1 with Y-Axis A2 and down, X-Axis B1 and across(otherwise, change ranges to suit your data):

=B2="completed"
=AND(COUNTIF(B2:$G2,"completed")<1,COUNTIF($B2:$G2,"completed")=1)

This worked perfectly. Do you mind breaking down the countif < scenario for me?

 

[dreid1011]

Glad it worked.

So, originally I tried COUNTIF(B2:$G2,"completed")>=1. This ended up highlighting just the rows where completed actually was, but from left to right up to the cell containing completed.

I changed the comparison to <1 which ended up highlighting the rows left to right, but starting at the cell containing "completed". This also highlighted any row where "completed" did not exist.

The second COUNTIF eliminates the rows where "completed" does not exist.

Now, the other reason this works this way is the cell locking. B2:$G2

As CF "drags" the formula across the row, the range that is being checked for "completed" is getting smaller, therefor eliminating the beginning cells of each row as the formula goes along.

 

[PETTEXCEL]

Ok that's helpful. I understand the less than placement now and how it eliminated rows where "completed" did not exist.

What I'm still unclear on is cell locking. Are you saying that because you placed the $ before G2 that this told the CF to not apply to any cells between B2:G2 where the condition does not exist?

 

[dreid1011]

Not quite. The $ locks the cell reference so that when you drag/copy a formula across rows/down columns, that reference will not change. You can lock a cell by row (A$1), or column ($A1) or both ($A$1). CF is smart will automatically adjust the formula to suit each cell in the range it is applied to.

For example: If I were to apply a rule of highlighting a cell if the value equals 1, and highlighted the range A1:B5, I would use a formula like this: =A1=1. Per the following table, this is how CF will interpret this formula for each cell in the selected range.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=A1=1[/TD]
[TD]=B1=1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=A2=1[/TD]
[TD]=B2=1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=A3=1[/TD]
[TD]=B3=1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=A4=1[/TD]
[TD]=B4=1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=A5=1[/TD]
[TD]=B5=1[/TD]
[/TR]
</tbody>[/TABLE]

Now, this next table will show how CF interprets the cell locking in the first COUNTIF I used:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]X-axis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Y-axis[/TD]
[TD]=COUNTIF(B2:$G2 ...[/TD]
[TD]=COUNTIF(C2:$G2 ...[/TD]
[TD]=COUNTIF(D2:$G2 ...[/TD]
[TD]=COUNTIF(E2:$G2 ...[/TD]
[TD]=COUNTIF(F2:$G2 ...[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]=COUNTIF(B3:$G3 ...[/TD]
[TD]=COUNTIF(C3:$G3 ...[/TD]
[TD]=COUNTIF(D3:$G3 ...[/TD]
[TD]=COUNTIF(E3:$G3 ...[/TD]
[TD]=COUNTIF(F3:$G3 ...[/TD]
[/TR]
</tbody>[/TABLE]

By the time the formula is applied to column G, the cell reference is G2:$G2, so it is only checking 1 cell at the end of each row.

 

[PETTEXCEL]

Great explanation. Thanks again!

 

[dreid1011]

You're welcome.