Defining a range of cells in a VBA hide/unhide column function

Per,

Thank you, works like a charm. Thanks again also to Don and JL, you guys are a credit to this online community.

JL and Don,

Mucho thanks for your suggestions.....I'll clarify what I am looking for, because I can't the solutions you suggest work.....I am getting compile or Run 13 errors. Here goes

I have 1 column "E", starting at row 4, where a market value is entered ie. 155$. Now, in column "F" I have a drop down list where the default shown value is "--". See first image.  

If the user selects one of the other values, say  "Mint", it will do a VLOOKUP on hidden column "G" and in column "H" will post the price of "E" multiplied by the % as determined in "G".....(155*10%)+ 155. The items priced in column E have various conditions in which they can be found, and the IF statement was way too complex to write which is why I chose this approach.

But the script only works for the original row shown. If the user inputs a value in the next row and selects a Manual CDN other than "--", no column appears, neither are calculations done.

One last question if I may. I am using the AVERAGE function in the Averaged price and I have been trying to use an IF condition basically stating that IF Media CDN = "Missing", then avg price = Estimated value of Media. ELSE average all the values.

Thank you very much for considering my question which may seem simple to you but rocket science to me :)

I appreciate the efforts, being a former MVP myself in another expertise.

Roch

In addition to what Don Guillett provided, I think what you're looking for is to make the row number in your Range() statements variable.  You can do that with a construct like this:

If Range("F" & Target.Row)="--" Then

You don't need the .Value in this case because .Value is the default property, and believe it or not, when you want a default property to be used, it's actually faster than specifying that same property.  We're only talking microseconds, but they can add up.  But if you're worried about clarity of understanding for the code, then using the .Value makes it pretty darned clear what you're examining.

Also, I'm not sure from your question but what you may be wanting to limit your code to run only when you select/change a cell in one specific column, E.  If so, you can add this to the test at the beginning of the code (incorporates Don's suggestion also):

If Target.Count > 1 Or Target.Column <> 5 Then

  Exit Sub

End If

That says that if more than one cell is selected OR if the selected cell is not in column E, then exit the sub without further action.

Finally, it may be that you need to make your tests after the user makes a data entry or choice from a list, and in that case you may want to use the Worksheet_Change() instead of Worksheet_SelectionChange() event to get the job done.

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

something like this

if target.count>0 then exit sub

if not intersect(range("a2:a22"),target))is nothing then

do your thing

end if

Don Guillett MVP Excel SalesAid Software *** Email address is removed for privacy ***