Conditional Formatting: Changing value to a minus if &quot

  • I have a simple sheet for logging incoming sales. The sheet has 6 columns

    A= Sales person
    B= Client
    C= Order value
    D= Date of order
    E= Follow up date
    E= Status

    In column E I have setup a simple cell drop down list with 4 options.

    1. Accepted
    2. Pending
    3. Rejected
    4. Cancelled

    I have used conditional formatting to give visual indication of the status. No points for originality but 1 = Green, 2 = Amber 3 = Red. My problem is scenario 4, I have run out of options in Conditional Formatting.

    I would like to be able to turn the font red, bold the type and make the original value entered into a negative value. ie. Original sale value was £2,000 would return -£2000 if last column is set to "Cancelled"

    Any ideas, I am stuck now!

    Thanks in anticipation


  • Thanks Derk, I have set the default to Red bold font and will tell the person that inputs the data to select the appropriate status. That way she can have all four visual prompts.

    As for creating a minus I have just added some hidden formulas that have simple arithmetic in them. I have then added a small formula to the summary page that subtracts the negative amount from the total.

    Crude but effective!


  • I thought this was solved but it appears that the person entering the data would prefer the value to turn to a minus if "Cancelled" is elected.

    e.g. If £1200 is entered in the value and then "Cancelled" is selected in the status column then it should change to -£1200.

    Can I do this normally or is a macro / VBA module required?



  • Hi Dave,

    OK, first a non-VBA suggestion (depends how user-friendly the user thinks it is!):

    Enter the order value in a different column, eg column I, then in the C column enter a formula along the lines of "=IF(E2="Cancelled",-I2,I2)"

    For a VBA approach - what version are you using? The reason I ask is that Excel97, which I have, does not consider Data Validation List changes to be changes for event code purposes (although there is a Calculation event workaround). The info on this link may prove useful :


  • Hi Richie

    I did something similar with extra columns and I tried your suggestion first up. The operator only wants to input the value in column C so that stops that idea.

    I then used an if statement and some simple arithmetic so that the value would be deducted on the final summary sheet. Once again this wasn't acceptable.

    Basically they need some code that will change the inputted value to a -ve if "cancelled" is selected in the F column.

    I have tried with my new found albeit limited knowledge but I can't get it off the ground.

    I am using Excel XP.



  • Hi Richie

    It took me a while to work out what the code was doing. I must admit I am still not 100% sure that I know how it functions down to the letter, but I managed to edit it so that it works.

    I have some extra columns in my sheet that I didn't mention in the original post - my fault entirely. I had to adjust the offset accordingly but now it seems to work fine. I noticed that the minus blinks until you select another cell but apart that it's great.

    Thanks for your help.


  • There appears to be a bug in the code?

    I have copied the code below. When you select cancelled from the Status Column the minus before the Value in Column C flashes on and off. If you then hit enter it is pot luck if the minus stays or disappears?

    Can anyone help me with this code and see the error?

    Option Explicit

    Private Sub Worksheet_Calculate()
    Const strLookFor = "Cancelled"
    If Application.Intersect(ActiveCell, Range("G1:G300")) Is Nothing Then Exit Sub
    If ActiveCell.Value = strLookFor Then
    With ActiveCell.Offset(0, -4)
    .Value = -.Value
    End With
    With ActiveCell.Offset(0, -4)
    If .Value < 0 Then .Value = -.Value
    End With
    End If
    End Sub

    The columns I am using are :

    A = Sales Person
    B = Client
    C = Sales Value
    D = Maintenance %
    E = Date received
    F = Follow up date
    G = Status (Cancelled, Pending, Accepted)

    I have edited the code wrong or there is a bug somewhere?


  • Richie

    I tried the code using DV on XL XP and it worked fine.

    The problem I can see is with user error when inputting data to the sheet. The minus signs appears to toggle when "Cancelled" is selected so if the user accidentally selects cancelled again when editing an entry and then realizes their mistake, it will change the value to a +ve one.

    Crazy I know but "Sod's Law" means it will happen :mad:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!