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.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]-[/TD]
[TD="width: 64"]-[/TD]
[TD="width: 64"]-[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]-[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mr2017]

Try:

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

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.

Ok, thanks Joe.

Should the over-riding rule be at the top?

And which one should have the "Stop if True" box checked? Apologies, I'm new to using Conditional Formatting in this way....

 

[Joe4]

Should the over-riding rule be at the top?

Yep, that is what I said in my previous reply:

So any "override" rules you will want listed at the top.

And which one should have the "Stop if True" box checked? Apologies, I'm new to using Conditional Formatting in this way....

If you have them listed in the Order you want them to proceed, with your overriding ones listed at the top, you probably want ALL of them to have that box checked, so once it meets a condition, it stops there.

 

[Mr2017]

Ok, apologies - I hadn't re-read your last post recently.

With the over-ride condition, I've tested it on a smaller set of data, but it doesn't seem to be working?

To recap, I used your solution

=COUNTIF($A1:$C1,"FALSE")>0 to make a row red IF the word "false" appears in the row (which works)

then I added

=AND(C1="FALSE",D1<>"") to make a row GREEN if both these conditions are true (which they are for the data below) and I listed this rule above the RED rule in the conditional formatting window, but the GREEN rule didn't work?

I'd also ticked the "Stop if true" boxes for both rules.

Do you know what else I'd need to change to make the GREEN rule over-ride the red one, please?

The data below can be copied and pasted in to cell A1, after which the word "FALSE" will appear in cell C2 and cell D2 will be blank. All other cells in the range A1:D3 will have a "-". Please let me know if you want me to clarify anything. Thanks

[TABLE="width: 256"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]-[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

One thing we forgot to do was add the Absolute Reference to the column parts of the formula, i.e.

[COLOR=#333333]=AND([/COLOR][COLOR=#ff0000][B]$[/B][/COLOR][COLOR=#333333]C1="FALSE",[/COLOR][COLOR=#ff0000][B]$[/B][/COLOR][COLOR=#333333]D1<>"") [/COLOR]

However, it still appears to be choking on the FALSE part. I think it is having issue with it because it could be the text value FALSE, which requires to be surrounded in double-quotes, and the boolean value of FALSE which does not. I think the formula may be converting it and failing out.

Where exactly does that value of FALSE in cell C2 come from?
Is it the result of a formula (if so, what is the formula), or is it hard-coded?
Is there any possibility of using other values instead like "F"?
The formula might work better if we don't confuse it with TRUE/FALSE.

 

[Mr2017]

Ah, ok - the word "FALSE" comes from a formula - it only comes up if the formula doesn't recognise the text in a cell in a preceding column.

I don't have the actual data with me now, but when I changed "FALSE" to "Review" in the sample data, the formatting worked as intended - full details below.


Used the data below.

Put a formula in cell C1 [=IF(B1="-","Review","")] and dragged it down to C3 then put the words "Now Fixed" in cell D2 and used

=AND($C1="Review",$D1<>"") in the cond formatting as the top rule and

=COUNTIF($A1:$C1,"Review")>0 as the second rule. Rows 1 and 3 are red, as expected and 2 is green, because it has the words "Now Fixed" (which a user would have to select from a drop-down (the actual words would be different). I'll have to see if I can get the formula to return a different value / text if it doesn't recgonise the text in a preceding column then base the cond. formatting on that value / text instead.



[TABLE="width: 265"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]-[/TD]
[TD]-[/TD]
[TD]Review[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]-[/TD]
[TD]Review[/TD]
[TD]Now Fixed[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]-[/TD]
[TD]Review[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mr2017]

Hi Joe

Quick question:

Do you know why the second condition for this conditional formatting isn't working?

I'm sure it's something simple! There are just two cells in this example: A1 and B1.

The first condition is this if this text ("ERROR: Select a mechanic from the drop-down in the next column") is in cell A1, then A1 and B1 turn RED. And this condition works using a variation of a formula you provided (=COUNTIF($A1:$K1,"ERROR: Select a mechanic from the drop-down in the next column")>0). I selected RED in the formatting of the cell, if this condition is true.

The second condition is that if the ERROR text above is in A1 and the words in B1 are NOT = to "Select a mechanic" then cells A1 and B1 should turn GREEN.

A user has to select an option from a drop-down in cell B1 that uses validation to ensure they choose a valid "mechanic."

The data validation list is in cells E1 and E2 and just has two values: Special Offer" and "Select a mechanic" (also below).

[TABLE="width: 134"]
<tbody>[TR]
[TD]Special Offer[/TD]
[/TR]
[TR]
[TD]Select a mechanic
[/TD]
[/TR]
</tbody>[/TABLE]

So if a user selects "Special Offer" then cells A1 and B1 should turn green, but they're not
turning green?

The conditional formatting formula for them to turn green is =AND($A3="ERROR: Select a mechanic from the next column",$B3<>"Select a mechanic")

 

[Joe4]

=AND($A3="ERROR: Select a mechanic from the next column",$B3<>"Select a mechanic")
Did you mean to use $A3 and $B3 instead of $A1 and $B1?

 

[Mr2017]

That's a good spot! Yes, I did!

However, changing those cell references doesn't seem to fix the problem....

Any other ideas, please?

 

[Mr2017]

Also, if the first row that I'm applying the formula to is row 3 in the real data (that's why there were references to row 3 in the error you spotted), should the formula start with row 3 in the real data, given that the real data starts from row 3 rather than row 1?

Thanks in advance.

 

[Joe4]

Also, if the first row that I'm applying the formula to is row 3 in the real data (that's why there were references to row 3 in the error you spotted), should the formula start with row 3 in the real data, given that the real data starts from row 3 rather than row 1?

Here is how you need to approach it:
What range are your first selecting to apply this Conditional Formatting formula to (remember how I said to first select the entire range that you want to apply this to, what range are you selecting)?
Now that you have selected the range, what is the VERY FIRST cell in that range?
So, whatever Conditional Formatting formula you write, has to be in reference to THAT cell.

Let's say that you had a real simple scenario where you want to highlight any row where the value in column B of that row is "YES".
Sometimes people select ALL the rows (starting with row 1). But maybe row 1 has a header, and the data starts on row 2.
So, they incorrectly determine that their formula should be: =$B2="YES"
However, since the first cell in their selection is A1, all cells in row 1 will look at cell B2, all cells in row 2 will look at B3, etc. So they are off one row.

If they want to use the formula: =$B2="YES", then they need to start their cell selection in row 2, not row 1.
OR, they need to write the formula as something like: =$B1="YES"

Make sense?
If not, tell me what range you are selected to apply the CF formula to, what formula you are using.