Apply Conditional Formatting Across Several Cells Solved/Closed

Question

Hello,

I want to change the color of a row if there is text in column A of that row.  I have the following equation: "=AND(ISTEXT($A$2))" ... and I have it applied to "=$2:$2"

It works perfectly for row 2.  But I want to apply it to my entire worksheet as this is a growing document that could end up being hundreds of rows long.  I cannot imagine the only way is to manually apply this conditional formula to every row - adjusting both the reference cell and the applied to row.  Is there a way to build this equation so that it automatically adjusts to the appropriate row?

Thank you!!

If helpful, this is for Excel version 2207
Windows / Chrome 104.0.0.0

TrowaD · Answer

Hi RDavis,

You are right, there is an easier way.

It starts with the formula you use. The AND part is unnecessary, but more importantly remove the dollar symbol in front of the 2. With the dollar symbol in place, the formula will keep referring to cell A2, even when applied to other cells, or in your case, rows.
Your formula should look like this:
=ISTEXT($A2)

Now over to the applied range. Since we now freed the number 2 of its dollar symbol, you can select the entire row and drag it down as far as needed. In your case it's probably faster to just adjust the formula:
=$2:$65536

By the way, how do you like Excel 2207? I'll probably (you never know) won't live long enough to experience it ;)

Best regards,
Trowa

rdavis · Answer

Amazing - this worked perfectly.  THANK YOU!

So far so good re: Excel 2207!!

Apply Conditional Formatting Across Several Cells

2 responses

Similar discussions

Subscribe To Our Newsletter!