4
I have an excel worksheet and two columns. I highlight the first column if the value of the cells are less than 0.
Now how can I highlight the second column cells based on the first columns that are highlighted?
See the image below :
Now I want to make my worksheet like this image:
Pooya Yazdani
Posted 2014-01-01T12:34:44.153
Reputation: 155
4
You can do it as follows (I'll assume that the 4.9755 is in cell A1, change as applicable):
Select the range of A1 to A10.
Pull up the conditional formatting dialog and select the option that requires a formula.
Put the formula =B1>0 and pick the formatting with font as green.
Press OK and repeat for the negative values, except using =B1<0 for the formula and the font as red for the formatting.
The number formatting doesn't need conditional formatting, you can simply select the two columns and format as appropriate.
If you want to format both columns at the same time, you can select both colums and use the formula =$B1>0 for green and =$B1<0 for red:
Jerry
Posted 2014-01-01T12:34:44.153
Reputation: 4 716
1
Jerry's answer is correct. Excel is adaptive, but you must make sure the formula looks exactly like he shows. If you click on cell B1 the formula will show =$B$1>0 rather than =$B1>0. The formula cannot contain the $ before the number 1 or it will not be adaptive. The $ symbol locks the formula, which is helpfull in other situations, but not here.
Brent Hadley
Posted 2014-01-01T12:34:44.153
Reputation: 11
Pooya provided a question and not an answer. Did you mean his/her accepted answer instead? – Pimp Juice IT – 2019-10-31T22:04:05.657
How can I apply to whole column, not one by one. You said "Select the range" and in step 3 you said "Put the formula =B1>0". I want to highlight A1 to A10 cells according to B1 to B10, but this formula highlights A1 to A10 according to B1, – Pooya Yazdani – 2014-01-01T13:05:37.557
@PooyaYazdani Did you try it? The conditional formatting adapts to the ranges, so that on A2, the actual formula that applies is
=B2>0. – Jerry – 2014-01-01T13:20:26.6931yes I tried it, it doesn't adapt a range to another range! you can simulate this table, I select the first range (A1-A10) an then choose highlight rule, then I select more rules, then Use a formula to determine which cells to format, and then I select second range (B1-B10), but the first range highlights according to first cell of second cell (B1), Is it true? you can simulate this table. – Pooya Yazdani – 2014-01-01T14:53:56.373
@PooyaYazdani I didn't make any mention of selecting the second range! Just put the formula I told in my answer in the box and you'll see! – Jerry – 2014-01-01T15:24:46.030
what do you exactly mean by step 2? which option? where should I put the formula? – Pooya Yazdani – 2014-01-01T16:06:38.017
@PooyaYazdani I added pictures. – Jerry – 2014-01-01T16:21:36.147
Great Appreciates Jerry. – Pooya Yazdani – 2014-01-01T16:44:04.677