Simple ClearContents not working

[ColinCoady]

I can't figure out why this won't work. Can someone take a look?

Private Sub ClearContents()
Worksheets("Costing").Activate
If Cells(7, 5).Value = "Other %" Then Cells(7, 7).ClearContents
End Sub

 

[My Aswer Is This]

You do understand:

[LEFT][COLOR=#333333][FONT=monospace]If Cells(7, 5).Value = "Other %" Then Cells(7, 7).ClearContents
[/FONT][/COLOR][/LEFT]

Means if Row(7) column(5) And then clear Row(7) Column(7)

These are not the same.

 

[ColinCoady]

Yes - I want one cell cleared based on the value in a different cell.
Can you help with this?
-Colin

 

[Fluff]

What is the value in E7 on the Costing sheet?

 

[MARK858]

Colin, check that the string in E7 has no leading or trailing spaces, also check that it is only one space between the r and the %.

 

[ColinCoady]

The value in E7 is from a Data validation list with 5 choices. One of the choices is "Other %"
I changed "Other %" to "Other%" (no space) and changed the code accordingly. Still doesn't want to work.
Still missing something - just not sure what. Any thoughts?
Thanks MARK858 and Fluff
-Colin

 

[MARK858]

None from me at the moment as long as it is G7 being cleared.

Just out of interest can you copy and paste (Please copy/paste it, don't retype it) the result that appears in your immediate window if you run the code below.

Sub testit()
Debug.Print "| &"; Cells(7, "E").Value; "& |", Len(Cells(7, "E").Value)
End Sub

 

[ColinCoady]

I got this
| &Other%& | 6

 

[MARK858]

Then I am afraid I have no ideas at the moment (if you don't have any other code taking effect and no protection, which would have produced an error) as your code is clearing G7 for me.

 

[James006]

Hello,

You could test following

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address <> "$E$7" Then Exit Sub
  If Target = "Other %" Then Target.Offset(0, 2).ClearContents
End Sub

Hope this will help