Conditional formatting for a whole row!

[Mr2017]

Hi

Does anyone know how to make a whole row red if a certain word appears in a cell?

Please find below a simple example:

The data below covers cells A1:C3 and every cell in that range has a "-" with the exception of cell C2, which has the word "FALSE."

I'd like to make the whole row red (from column A to column C) IF the word "FALSE" appears in a cell in that range.

I've tried to highlight the whole range, then gone to Conditional formatting and selected "use a formula to determine which cells to format" but I'm not sure what formula should be used in this instance.

Thanks in advance.

-	-	-
-	-	FALSE
-	-	-

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Joe4]

When applying to a whole range of cells, first, select the whole range that you want to apply the Conditional Formatting to.
Then, write your formula as it appears to the very FIRST cell in your selected range.
So, in this case, select A1:C3, and use the following CF formula:

=COUNTIF($A1:$C1,FALSE)>0

if it is using the boolean value of FALSE
or

=COUNTIF($A1:$C1,"FALSE")>0

if it is using a text value of "FALSE".

Note that we put the dollar sign if front of the column reference to "lock" that down, but not in front of the row reference to allow that part to "float".
See: http://www.cpearson.com/excel/relative.aspx

 

[Mr2017]

Excellent!

That worked on both the sample range of data and the extended data that I have, after I modified the cell references but used your Logic!

Thanks Joe4!

 

[Joe4]

You are welcome!

 

[Mr2017]

By the way, do you this this would slow a file down significantly, if the conditional formatting was applied to 10,000 rows?

 

[Joe4]

By the way, do you this this would slow a file down significantly, if the conditional formatting was applied to 10,000 rows?

Not sure, but there is one way to find out -- give a try and see what happens!
In general, I would think using Conditional Formatting should be faster than using VBA to do this.

 

[Mr2017]

Ok.

My laptop is misbehaving, at moment.

So I thought I’d check with you, in the meantime.

But I’ll test it later.

Thanks

 

[Joe4]

Off the top of my head, I do not know. I would have to test it out.
But there are also many factors which come into play, like how many columns you are applying it to, how big the file is, if you have other Conditional Formatting, if you have any VBA, etc.

 

[Mr2017]

Hi Joe

Do you know how I can modify the formula you've written so that the row would become GREEN

IF the word "FALSE" remains in a cell in column N (let's say N3), BUT cell P3 (which would normally be blank if N3 had the word "FALSE") now had a value or phrase in it, and was no longer blank?

I'd like to create a rule which overrides the "FALSE" rule (=COUNTIF($A1:$C1,"FALSE")>0) if a cell in column P in the same row is manually changed to a non-blank" value by a user.

The reason the word "FALSE" would appear in column N is because a function that they've described in column D doesn't make sense or isn't spelt correctly. They'd they have to select the correct function in column P (from a drop-down list as column P has data validation which restricts the options a user can select) after which the row should then turn green.....

Please let me know if you want me to clarify things further.

 

[Joe4]

IF the word "FALSE" remains in a cell in column N (let's say N3), BUT cell P3 (which would normally be blank if N3 had the word "FALSE") now had a value or phrase in it, and was no longer blank?

Try:

=AND(N3="FALSE",P3<>"")

I'd like to create a rule which overrides the "FALSE" rule

Note that if you go to "Manage Rules", you can list the order that you want these rules to be processed in, and if you check the "Stop if True" box, that means if that rule is met, it will stop there for that cell and not check any of the rules listed below it. So any "override" rules you will want listed at the top.