I want to find all the cells > 100 in the area 'A1:E13', then highlight the row the cell lying. I used the new rule of Conditional formatting with formula '=$A1:$E13>100'. It seems work for a single column, but not work for an area.
2 Answers
Clear any existing formatting from A1:E13 select that range and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::
=OR($A1>100,$B1>100,$C1>100,$D1>100,$E1>100)
Format..., select formatting, OK, OK.
(Will also highlight any rows containing Text. "Row" here is five columns wide.)
Without using any addtional column, you have to use this formula for the conditional formatting :
=(MAX($A1:$E1)>100)
Please be careful with the absolute (with $) and relative references, and do not forget the () around the formula.
Please also note that you have to select your area from top to bottom, i.e. you click into A1, hold mouse button and move down and right with the mouse, and release mouse button over E13 (which is probably the natural way of doing it!); if you start from the bottom (e.g. click in cell E13 and release over A1), then you have to test in the Conditional formatting =(MAX($A13:$E13)>100). This is because the conditional formatting takes the cell which is highlighted within the selected area as starting point for relative references to evaluate the formula.
If you want to use an additional column (e.g. F) in your worksheet (which you can hide later of course), it is even easier:
- In cell F1 you put MAX(A1:E1)
- With conditional formatting ("Use a formula to determine which cells to format"), you test whether $F1>100 for your entire range.
Related