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!

 

[villy]

can you provide some sample?

 

[RichardMGreen]

You could use the Worksheet_Change event to check what has just been entered.
You can then use a Select Case statement to choose which cells to block out which is also dependant on what has just been entered.

Using the Worksheet_Change event means it fires every time something is entered onto the sheet (although you can reduce it to just firing on changes to certain cells) and the Select Case statement means you don't have a load of IF statements to write.

 

[Mally1975]

You could use the Worksheet_Change event to check what has just been entered.
You can then use a Select Case statement to choose which cells to block out which is also dependant on what has just been entered.

Using the Worksheet_Change event means it fires every time something is entered onto the sheet (although you can reduce it to just firing on changes to certain cells) and the Select Case statement means you don't have a load of IF statements to write.

lol thank you for that.. Just need to work out all the coding for this!!.. Coding and I are not the best of friends, you see ..

 

[RichardMGreen]

If you can supply some sample data and a spreadsheet layout, I'll see what I can do.

 

[Mally1975]

If you can supply some sample data and a spreadsheet layout, I'll see what I can do.

Of course, what's the best way to do that?..

 

[RichardMGreen]

See this thread on showing sample data.
http://www.mrexcel.com/forum/showthread.php?t=89356

 

[Mally1975]

See this thread on showing sample data.
http://www.mrexcel.com/forum/showthread.php?t=89356

Unfortunately, after repeated attempts, I just can't seem to get either of my PCs to accept this add in *sigh*.. Sorry..

 

[RichardMGreen]

Sorry for the delay, but this should get you started.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column = 2 Then
        Range("D" & Target.Row & ":H" & Target.Row).Interior.ColorIndex = xlNone
        Select Case Target.Value
            Case "A": Range("D" & Target.Row).Interior.ColorIndex = 5
            Case "B": Range("E" & Target.Row).Interior.ColorIndex = 5
            Case "C": Range("F" & Target.Row).Interior.ColorIndex = 5
            Case "D": Range("G" & Target.Row).Interior.ColorIndex = 5
            Case "E": Range("H" & Target.Row).Interior.ColorIndex = 5
        End Select
    End If
    Application.EnableEvents = True
End Sub

It's not the tidiest of code, but it works.
You may want to change the colorindex number (it's currently blue). You'll also need to change the A,B,C,etc to what will actually be going into each cell.

To get this to work, if you right-click on the sheet tab and select "view code", you can just paste it straight in.

Let me know if you have any problems with it.

 

[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..