Share via

How to color a cells contents (red) negative or (green) positive which is already using a formula to get its result?

Anonymous
2017-12-03T14:03:21+00:00

Hi thanks in advance for any help :)

Im running a formula in a cell (D3) which gets live data fed in every 15mins. I need the result in the cell to show me when its a positive or negative figure

by changing colour to either green or red. Ive been trying a number of different ways but cant make it work and im not sure if its because the cell ha a formula already or maybe that downloaded info to the cell is a percentage. The cells code works fine I just need this last step to tweak it so I can at a glance see the changes happening, not sure if its something I can add to the code?.

=VLOOKUP(D2,CMC!$B$2:$O$1321,11,FALSE)

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-12-03T15:04:48+00:00

    You have to use the Conditional Formatting.

    Highlight the cells that contain the numbers to be colored**.**

    Go to Home >> Styles >> Conditional Formatting >> Highlight Cells Rules

    Apply Greater than 0 and Less than 0 rules on the same range, and select the color that you want to format these cells when the condition is TRUE.

    50+ people found this answer helpful.
    0 comments
  2. Anonymous
    2017-12-03T15:53:15+00:00

    In Less Than dialog box type 0 in the condition box and choose Light Red Fill with Dark Red Text or a custom color as shown in the below screenshot:

    Do the same this in the Greater Than dialog box, but change the color to green, and don't forget to apply the two rules on the same range.

    10+ people found this answer helpful.
    0 comments
  3. Anonymous
    2017-12-03T16:10:13+00:00

    If this does not work, then the problem is:

    These numbers are not treated as numbers; they are treated as text, so you need to parse them!

    I see them aligned to the left as the screenshot!

    Sure they are texts!

    To quickly solve this problem, go to the end of VLOOKUP and type: +0 then hit Enter as follows:

    =VLOOKUP(D2,CMC!$B$2:$O$1321,11,FALSE)+0

    Then copy this formula to adjacent cells.

    9 people found this answer helpful.
    0 comments
  4. Anonymous
    2017-12-04T00:20:07+00:00

    If this does not work, then the problem is:

    These numbers are not treated as numbers; they are treated as text, so you need to parse them!

    I see them aligned to the left as the screenshot!

    Sure they are texts!

    To quickly solve this problem, go to the end of VLOOKUP and type: +0 then hit Enter as follows:

    =VLOOKUP(D2,CMC!$B$2:$O$1321,11,FALSE)+0

    Then copy this formula to adjacent cells.

    Ah your on the money well done adding the +0 to the end made it all work problem solved thanks Haytham ;)

    2 people found this answer helpful.
    0 comments
  5. Anonymous
    2017-12-03T15:37:21+00:00

    Hi Haytham unfortunately its working with the Greater than function but the negative numbers are green too.

    If I do the Less than function first then nothing happens. So im not really sure this is working as intended even for the 

    positive figures or just if its just colouring the cells and text since its not seeing a difference between the 2? Cheers

    2 people found this answer helpful.
    0 comments