Trying to create a Macro to put a zero in certain cells of the current row.

[NickCourtney6]

Hello, I've tried this many different ways. I have in Column A a drop-down list of statuses:

Requested

Pending

Approved

Denied

Purchased

Received

Verified

Status	Quantity Requested	Quantity Approved	Gear Name	Unit Cost	Ext Price	Other Costs	Other Costs Price	Shipping Costs	Estimated Purchase Date	Estimated Subtotal	Link
Requested	2	2	Blackmagic Decklink 8k Pro	$745.00	$1,490.00	case	$200.00	$10.00	10/1/23	$1,700.00	www.yahoo.com

When "Denied" is selected I want specific monetary cells within this row to equal zero but do not clear the content of the whole row. I would like to do this automatically once Denied is selected. However, if it must be a button, I'm okay with that too. Any help is greatly appreciated.

 

[BigBeachBananas]

Try this. Paste the VBA in the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KeyCells As Range
   Set KeyCells = Range("A:A") 
 
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
       If Target.Value = "Denied" Then
           Target.Offset(0, 4).Value = 0 ' Column E
           Target.Offset(0, 5).Value = 0 ' Column F
           Target.Offset(0, 7).Value = 0 ' Column H
           Target.Offset(0, 8).Value = 0 ' Column I
           Target.Offset(0, 10).Value = 0 ' Column K
       End If
   End If
End Sub

 

[NickCourtney6]

This worked somewhat. However, the columns I would like to be zeroed out if Denied is selected are Columns B, C, E, H, J, and K.

 

[BigBeachBananas]

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KeyCells As Range
   Set KeyCells = Range("A:A") 
 
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
       If Target.Value = "Denied" Then
           Target.Offset(0, 1).Value = 0 ' Column B
           Target.Offset(0, 2).Value = 0 ' Column C
           Target.Offset(0, 4).Value = 0 ' Column E
           Target.Offset(0, 7).Value = 0 ' Column H
           Target.Offset(0, 9).Value = 0 ' Column J
           Target.Offset(0, 10).Value = 0 ' Column K
       End If
   End If
End Sub

 

[NickCourtney6]

This will do it! Thank you so much.

 

[NickCourtney6]

This will do it! Thank you so much.

I am getting a Run-Time error '1004': Method 'Value' of object 'Range' failed?

 

[NickCourtney6]

Looks like it's due to a locked column with the sheet being protected. Any way around this?

 

[NickCourtney6]

Disregard. At this time, I've got it sorted out. Thank you again for your knowledge and help!