# Change value to an alternative value

Closed
Meganlawrence Posts 1 Registration date Wednesday May 7, 2014 Status Member Last seen May 7, 2014 - May 7, 2014 at 09:48 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 8, 2014 at 11:22 AM
I am wanting the instructions to design a formula so that each time a value eg \$90.00 appears in a cell that the system will automatically change the value from \$90.00 to \$100.00.

I am struggling with an =IF to accommodate this.

There will be three values that may appear so the formula would have to be :

if \$90.00 appear it changes to \$100.00, if \$80.00 appears it changes to \$85.00 and if \$70.00 appears it changes to \$60.00. These values will appear in the same column within a range of around 20 cells. Therefore from L4....L24.

Any assistance at all is so greatly appreciated.

## 1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 8, 2014 at 11:22 AM
Hi Megan,

You can not put a value and a formula in a single cell.

For that we need to create a macro.

Copy the code below, right-click on the sheets tab and select View Code.
Paste the code in the big white field, which you can then close.

```Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("L4:L24")) Is Nothing Then Exit Sub
If Target.Value = 90 Then Target.Value = 100
If Target.Value = 80 Then Target.Value = 85
If Target.Value = 70 Then Target.Value = 60
End Sub```

Back at Excel try entering the specified values in the specified range to see if it works as you foresaw.

Best regards,
Trowa