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

Dave

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!

Dave

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?

Cheers

Dave

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.

Cheers

Dave

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.

Dave

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
Else
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?

Dave

I have attached an amended sheet that shows all of the columns in use. I am not sure which code version I should use with Excel XP.

Cheers

Dave

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:

Dave