I attempted to find a solution in this forum and online, but being out of the loop for so long, my Excel skills are a little rusty.
I am working on a spreadsheet where if I enter a value in a certain cell, in this case, B3, I want cells R3:T5 to be cleared since if any of the cells in a unique range have data in them, the value in another cell value gets a #VALUE Error due to a different calculation. I have various 3x3 ranges of cells that DO NOT have formulas in them, but whose values are used in other formulas that get the #VALUE Error if there are any values in those cells if I can't clear that 3x3 set of cells. The workbook only has 2 sheets presently, but all the VBA will be on the first sheet. Due to the size of the workbook, it is difficult to attach the file.
I believe I need to write a VBA event using a Worksheet_Change(ByVal Target As Range), but don't know how to use it correctly and eventually need to nest the IF statements in it.
I was thinking something like this would work, but it doesn't. I actually need 81 nested ElseIf Statements in it where the ... is.
Obviously, once I can get the first 2 If statements to work, I can write the other 79!
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "B3" Then
Range("R3:T5").ClearContents
ElseIf Target.Address = "C3" Then
Range("U3:W5").ClearContents
...
End If
End Sub
Any help would be greatly appreciated.If I am totally on the wrong track, I take no offense. I've been out of the loop for a while!