Excel – Conditional Formatting in excel based on another column

microsoft excelmicrosoft-excel-2007microsoft-excel-2010worksheet-function

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 :

enter image description here

Now I want to make my worksheet like this image:

enter image description here

Best Answer

You can do it as follows (I'll assume that the 4.9755 is in cell A1, change as applicable):

  1. Select the range of A1 to A10.

    enter image description here

  2. Pull up the conditional formatting dialog and select the option that requires a formula.

    enter image description here

  3. Put the formula =B1>0 and pick the formatting with font as green.

    enter image description here

  4. Press OK and repeat for the negative values, except using =B1<0 for the formula and the font as red for the formatting.

    enter image description here

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:

enter image description here

Related Question