CODEWITHSUNDEEP
excelexcel-formula
Snuffle
Snuffle

Reputation: 3

Is there a way in excel to highlight a row, only if one condition is true and another condition is false?

I have done quite a bit of searching on this, and have found a way to set up nearly every kind of formatting rule except for this specific case. I need an entire row of the table to be highlighted only when two different conditions are present. The two conditions are:

1 cell in column E does not contain “text 1”

2 cell in column H does contain “text 2”

I need the whole table (upwards of a few thousand rows, by 12-ish columns) to be considered, and I can’t modify any of the data in the tables, or the sheets themselves.

After some playing around with solutions to similar but different problems, I ended up with a formula that seems like it should work... the logic seems right, and I’m pretty sure the syntax is correct also, but when I add the formula to the conditional formatting rule, nothing happens. It accepts the formula, but it just does nothing.

I tested it out by applying the formula to a simple test sheet and put it in column C, and it results in “TRUE” being in column C when the conditions are present, and “FALSE” being in column C when one or both of them are not present, which seems like it’s behaving correctly. Any tips would be appreciated!

Here’s what I have currently:

=AND(IF(IFERROR(SEARCH(“text 1”, $E1), 0),FALSE,TRUE),IF(IFERROR(SEARCH(“text 2”, $H1),0),TRUE,FALSE))

(Edit: there are asterisks before and after the “text 1” and “text 2”, inside the quotes.)

Upvotes: 0

Views: 49

Answers (1)

SJR
SJR

Reputation: 23081

When using a formula in CF you must produce a formula which evaluates to either TRUE or FALSE, as in

=AND(ISERROR(SEARCH("text 1", $E1)),ISNUMBER(SEARCH("text 2", $H1)))

Upvotes: 0

Related Questions