Conditional Formatting, =A="", then stop conditional formatting for row

Question

I am fairly novice when it comes to excel and am trying to establish some formatting rules for a workbook at work to help with tracking.

For some details, Column A contains employee names, Columns B-Z contain various data points that we track per employee. I have a conditional formatting rule that if cell is blank for B:Z then highlight it.

This has unintentionally caused entire rows to be highlighted, which I don't really want, so I am trying to write a rule that would pretty much dictate, "If A is blank, then stop conditional formatting for row." Or for example, "If A75 is blank, then stop conditional formatting for Row 75."

I have tried doing =A="" with conditional formatting of no fill and "If True Then Stop" but it just does not seem to work.

Any pointer and/or visuals would be great!

Answer 1

The rule is written for the range to be formatted to begin in B2. It appears that you included Column A. Start your range in B2 and go to your end. All you need is this rule because Column A is not intended to be formatted, but the rule keeps any other cells in a row with a blank in Column A from being formatted.

Answer 2

Hi LGleason. I am an Excel user like you.

Here is a formula to use in the Conditional Formatting. I included B2: Z7 in the range to be formatted. Adjust the range for the number of rows you need included.

=AND($A2<>"",B2="")

Answer 3

Hi Rich,

I'm not too sure that your solution addresses the problem that I am having, allow me to demonstrate the issue I am having...

See the conditional formatting rules I have...=A2="" is stop if true, and has no highlight as its conditional formatting. This is for range A-I

Below that I have if cell contains a blank value than highlight yellow. This is for range A-I.

I am very confused as to why this command is not functioning properly. I am seeing seemingly random exceptions to the highlighting rule.

What I am trying to accomplish is for every row to be like Rows 3 & 4 if they have blanks, and row 12 if they do not have blanks.

I will be honest, when I applied your formula, I am met with strange results and I do not fully understand the goal of the formula; See below.

Answer 4

It appears in your screenshot that your data goes down to Row 20, so you should select B2:I20 before entering the formula in the Conditional Formatting.

=AND($A2<>"",B2="")

In the formula, the AND function means that both things must be true statements in order to apply the formatting.

$A2<>"" says that there must be something in the row in Column A or the entire rule is false for that row, and nothing will be formatted. The $ sign doesn't allow the reference to be changed to adjust to other cells as the formula is applied across the row.

B2="" says that any cell that is blank should be formatted (unless the first condition of data in Column A is false). There are no $ signs because we want Excel to adjust the cell references to other cells as the formula is applied across the row.

Your results should resemble the results I posted in the answer. All cells from Column B to I that are empty should be formatted unless there is nothing in that row in Column A. In that case there should be nothing in the row that is formatted.

Answer 5

I see now. I think my initial issue was that I was hung up on the formatting being generally applied to the entire row/column, which was leading to issues. Thank you for clarifying and helping!