Is it possible??..

[Mally1975]

Hi all,
Not sure if this is possible but what I'm trying to do is change the contents of a cell if that particular column is not relevant (based on Type).. I don't want to put a formula in the actual cell as I need it for possible data entry.. Is there a procedure or macro that would do this more effectively?..

My worksheet goes something like this:
Column A: Set
Cloumn B: Type
Column C: Unit

Columns: D through to H are only relevant to certain Types and those that are not, I have used conditional formatting so if the result is N/A the cell is 'blocked out' (which I have to enter manually).. I would like Excel to 'lookup'? Type and if A, then block out *cell reference* as not relevant and if it's Types B to G then block out *cell reference*.. The main problem being there are 8 different types..

Please help!

 

[Mally1975]

Thank you for your help on this - it all works fine.. Now just to mess it up and get completely stuck again with my worse than rubbish coding skills..

OK, it's no good.. Despite all your best efforts, I'm going to have to request that someone write the code I need for me...

 

[RichardMGreen]

What's the problem with it? I might be able to adjust the code on the fly.

 

[Mally1975]

What's the problem with it? I might be able to adjust the code on the fly.

Sorry for the delay!!..

This is the code I have at the moment (I found it in a book but I can't get it to work).. I also need it to put the value "N/A" into two columns based on unit type - not just the one.. Am I heading in the right direction yet?.. If the input in column B is from a lookup formula, could this be why I'm having problems?

Private Sub UnitType_Change(ByVal Target As Range)
Const UnitTypeCheck_RANGE As String = "B4:B10000"
On Error GoTo UnitType_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

Select Case UnitTypeChecker

Case "A": .Offset(0, 2).Value2 = "N/A"
Case "B": .Offset(0, 3).Value2 = "N/A"

End Select

End With

End If
UnitType_exit:
Application.EnableEvents = True
End Sub

 

[RichardMGreen]

You're heading in the right direction but you haven't told the code what UnitTypeChecker is.
Try replacing it with ".value" (without the quotes) and see what that gives you.

 

[Mally1975]

You're heading in the right direction but you haven't told the code what UnitTypeChecker is.
Try replacing it with ".value" (without the quotes) and see what that gives you.

Thank you for all your help, I had a play with the formula and at length got it to work.. I'm so sorry I haven't had time to thank you before this!.. Mx