I am looking to create a conditional formatting function that will highlight "C2:E2" if "E2" > 5000 & "B2" does not contain the word "treasury".

any help would be appreciated!

CodePudding user response：

Navigate in top menu to Format -> Conditional formatting There you can specify range as E2:E29

Use conditional formatting with just one AND formula that refers dynamically to the values of the same row, but different column AND comparision of the current value

=AND(indirect(address(row();column()-3))<>"Treasury";indirect(address(row();column()))>5000)

This can maybe be simplified, but definitely works with more conditions at once.

Some explanation: To avoid typing new formulas for each row, we use INDIRECT joined with ADDRESS

column()-3 refers to current column (the one you call formula in) . Have in mind that means for each columns (C->E) you need a different formula that refers to another column for checking if it contains "treasury". In your case you'll need different formulas for column C,D and E. column()-3 works for E, because Treasury is in B column (3 columns earlier from E)

P.S: translating from my native language, so not sure if English UI is the same, but should be intuitive from here.

CodePudding user response：

You'll want a custom Conditional Formatting Rule.

Click in cell C2, then from the menu choose:

Format > Conditional formatting

In the dialog box, set your range:

C2:E29 (or change 29 to whichever row is the last one you want to include in the formatting rule)

Under Format rules > Format cells if..., choose the bottom option from the dropdown menu: "Custom formula is."

In the field below, enter this formula:

=AND(NOT(ISNUMBER(SEARCH("treasury",$B2))),$E2>=5000)

Set your formatting style and click Done.

CodePudding user response：

try on range C2:E:

=($E2>5000)*(NOT(REGEXMATCH(LOWER($B2), "treasury")))