Change a range of cells based on value in specific cell using VBA code

[nathandavies]

Hi All,

I have some code which is working great but i want to make a slight change and i'm struggling. at the minute a range of cells change when you select a cell but i want to change it so if ProjectElecEngCol = N/A then open the msg box and if yes is selected put N/A in the range of columns as per my code below.

I have put my current code so you can see what i have done so far.



If Target.Column = ProjElecEngCol Then
If MsgBox("No Electrical Engineer:" & vbCr & "Would you like to force all Electrical deliverables to 'N/A'?", vbQuestion + vbYesNo + vbDefaultButton2, "No Electrical Engineer") = vbYes Then
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, SysArchCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, SchemesCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, LoopsCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, TechFileCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, ElecOrderCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, ElecReqCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, ShopFloorFileCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, TestCol) = "N/A"
End If
End If

[offthelip]

have you tried:


If Target.Column = ProjElecEngCol Then
If Target.value="N/A" then
If MsgBox("No Electrical Engineer:" & vbCr & "Would you like to force all Electrical deliverables to 'N/A'?", vbQuestion + vbYesNo + vbDefaultButton2, "No Electrical Engineer") = vbYes Then
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, SysArchCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, SchemesCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, LoopsCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, TechFileCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, ElecOrderCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, ElecReqCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, ShopFloorFileCol) = "N/A"
Excel.Workbooks(WorkbookName).Worksheets("WIP").Cells(Target.Row, TestCol) = "N/A"
End If
End If
End If




( Not Tested)

[nathandavies]

This has worked perfectly!

Thank you!