VBA method of copying values from RAND() to a range based on values in another range

[himey77]

Take a look at the attached workbook.

• Columns F, G, H contain the percentages which correspond to the options selected by the user in Columns C, D, E

• Cells F4, G4, H4 contain the maximum value of these percentages.

• Columns A and B are conditionally formatted so that the option and percentage cells corresponding to this maximum percentage are highlighted.

• Pressing the F9 key recalculates the various random numbers on the worksheet, and the appropriate option and percentage cells are again highlighted

I need the "Chosen SPP" field to be locked in once data is added to the next column. For example, once the user has acheived a result in C5, that result will be locked into C5. The user will then move on to Column D, select their options and acheive a result in D5. That result will be lock into D5. The user will then move on to Column E... etc. etc.

The RAND() function will give you a new set of data every time the spreadsheet recalculates. As long as you're relying on the RAND() function as the source of your data, it will place the problem of "locking in" the selected values well beyond the capabilities of any LOOKUP formula or conditional format solution. Especially if you intend to do this with multiple examples.

I recommend you begin a new thread with the suggested title of: "VBA method of copying values from RAND() to a range based on values in another range".

Hopefully one of the VBA experts here can help you.

Good luck,