conditional formating sums in brackets

[ekoot]

greetings Fellow Knights of the Round table.

I've looked carefully through the forum but have been unable to find a similar thread.

I've got an excel spreadsheet that includes sums in a bracket. I would like to use a formula or conditional formating to highlight the cell,( and ideally the following four cells in the column) where the number in the bracket is over ten. I've tried Conditional formating for number great then ten, but i fear that the text proceeding the number in brackets is not allowing the appropriate formating.

your advice and wisdom is appreciated, gurus go! example attached. greater than10.xls

[dilipandey]

Hi ekoot,

You can use the below formula in conditional formatting:-

Please Login or Register  to view this content.

see attached:- greater than10.xls

Regards,
DILIPandey

<click on below 'star' if this helps>

[ChemistB]

Try this formula, assuming your data is in A1:F35
Conditional formatting>Use Formula
=SEARCH("[??]",A1) format as needed

To exclude 10
=AND(SEARCH("[??]",A1), NOT(SEARCH("[10]",A1)))
Does that work for you?

EDIT; Modify second formula (thanks for the heads up from benishiryo)
=AND(SEARCH("[??]",A1), NOT(ISNUMBER(SEARCH("[10]",A1))))

[Ace_XL]

Taking ChemistB's solution further ..
In Cell D5 CF use..

Please Login or Register  to view this content.

this will highlight all 5 cells as required

[benishiryo]

hi ekoot. also a modification from ChemistB:

make sure you highlight from the top left to the bottom right of your selection (A1:F35) & go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=AND(OR(ISNUMBER(SEARCH("[??]",OFFSET(A1,-4,,5)))),NOT(ISNUMBER(SEARCH("[10]",OFFSET(A1,-4,,5)))))