Apply Conditional Formatting Across Several Cells

Solved
RDavis - Sep 8, 2022 at 06:19 PM
 rdavis - Sep 12, 2022 at 12:30 PM

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

2 replies

TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Sep 12, 2022 at 12:00 PM

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


1

Amazing - this worked perfectly.  THANK YOU!

So far so good re: Excel 2207!!

1