View original (French)

EXCEL_Conditional formatting - Adjacent cells

Solved
JonBid Posted messages 7 Status Member -  
Pocampe33 Posted messages 63 Status Member -
Bonjour,

I have an Excel file where I would like to insert conditional formatting, but with a specific condition. I have done my research without finding anything.

So I have 2 columns: the first mentions quantities, the second a sum of values (which are in other columns). What I would like is that when the value of this sum is equal to the quantity of the adjacent cell, the fill becomes GREEN and when it is different, it becomes RED.

However, I would like this condition to apply in relation to the cell just before! I emphasize that I know how to do the formatting. My question is therefore:
HOW CAN I APPLY THESE CONDITIONS TO ALL THE LINES OF MY TABLE (which would save me a lot of time).


Best regards,
JB

2 answers

  1.
    Pocampe33 Posted messages 63 Status Member 15
     
    Hello,

    Here are the manipulations (performed with Excel 2007):
    - Select the "Total" column corresponding to column B below
    - Click on the [Home] tab, then "Conditional Formatting" in the "Styles" group
    - Select "New Rule..." then "Use a formula to determine which cells to format"
    - Type this formula: =AND($B2=$A2;$A2<>"";$B2<>"")
    - Click on the [Format] button then on the [Fill] tab
    - Select the green background
    - Click on Ok twice
    - Verify the result
    - Create a 2nd rule in the same way, keeping column B selected
    - Formula: =AND($B2<>$A2;$A2<>"";$B2<>"")
    - Format: red background
    - Click on Ok twice
    - Verify the result
    - Observation: cell B2 corresponding to the title "Total" has a red background. To correct this, click on "Conditional Formatting" then "Manage Rules..."
    - In the "Applies to" column, paste the range: =$B$2:$B$999
    - Do the same for both formatting rules.
    - Click on Ok and verify the result

    Hoping this suits you

    Have a nice day,

    Pocampe33
    2