Select range of cells based on 1 cell value

[dairin]

I am trying to figure out a VBA to select range of cells in same row based on a value in a cell.
Example: let's say A1 cell has "5" as the cells value then it will select the 5 adjacent cells (B1:F1) and enter "OK" to those 5 cells Then then VBA will keep checking A2 on wards for value n do the same as above but if it found first blank cell in column A then the VBA stop

[jeffreybrown]

How about this...

Please Login or Register  to view this content.

[dairin]

thanks alot....it works....
I try to make it work with worksheet change event but it just don't work or crash the excel
any idea why?

[jeffreybrown]

I'm guessing you are saying, as you enter data in column A you want the macro to fire?

Please Login or Register  to view this content.

Place this code in the sheet module

[dairin]

I'm guessing you are saying, as you enter data in column A you want the macro to fire?

either I key in data manually or the data in column A is being changed with some formula in other cell

[jeffreybrown]

either I key in data manually or the data in column A is being changed with some formula in other cell

You can't have it both ways. Manually entered data and data changed via a formula are two different actions.

Well, maybe you can have it both ways, just not sure how to go about it. In the end, you need to map out the process so we can build a solution to fit.

[dairin]

ok then let me try myself first....thanks alot
just 1 last question.....how to ensure that if the number that I enter is smaller than the previous number then it will delete the extra "OK"
example: 1 enter 5 at first then it shows 5 "OK" but when I enter 3 in the next round, it will delete 2 "OK" and just show 3 "OK"

[jeffreybrown]

What if you just clear a certain number of cells? This will clear 100 cells to the right of the target cell. Column A.

Would this work?

Please Login or Register  to view this content.

[dairin]

It works like a charm.....thanks alot......

[jeffreybrown]

You are very welcome and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[jeffreybrown]

Note:

Just in case you don't want to go all the way out to 100 column since you may have information out there not to be messed with, try this on a copy of your data

Please Login or Register  to view this content.