Highlight cells with multiple criteria

[LearningExcel86]

Hi, I am trying to highlight data based on if the amount is >=4000000 but <=6000000 and also if another column contains the words “below” or “over”. I have played about with some formulas in the conditional formatting but can’t seem to get it. The amount is under column F and the word under column G. Any ideas?

 

[DanteAmor]

How about:

=AND($F2>=4000000,$F2<=6000000,OR($G2="below",$G2="over"))

 

[DanteAmor]

Or maybe you need something like this:

=AND($F2>=4000000,$F2<=6000000,OR(ISNUMBER(SEARCH("below",$G2)),ISNUMBER(SEARCH("over",$G2))))

 

[LearningExcel86]

How about:

=AND($F2>=4000000,$F2<=6000000,OR($G2="below",$G2="over"))

Thank you for this, column G would have more text than below or over, how can I add in *contains*?

 

[DanteAmor]

Thank you for this, column G would have more text than below or over, how can I add in *contains*?

I got it, and I sent you a new answer in post #3

 

[LearningExcel86]

I got it, and I sent you a new answer in post #3

Fab, thank you very much ?

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[LearningExcel86]

Or maybe you need something like this:

=AND($F2>=4000000,$F2<=6000000,OR(ISNUMBER(SEARCH("below",$G2)),ISNUMBER(SEARCH("over",$G2))))

I’m wondering how this can apply to all rows? Seems to work on row 2 but not all the way down the data set

 

[DanteAmor]

Edit the conditional formatting and in the "Applies to" field write the range of cells.

 

[LearningExcel86]

Edit the conditional formatting and in the "Applies to" field write the range of cells.


View attachment 43541

Hi, I have tried this morning but doesn’t seem to go any further than the first line. Can’t get the sample uploaded as on this thread on my phone and the file is on my work laptop which can’t get connected to these websites.