Random colour change on cell content input

Hi

I have a spreadsheet where we insert the time when a driver books out to start their delivery route.

I am looking for a way where a cell will randomly turn red to indicate the vehicle is to be searched before departure. There is a need for a minimum 5 searches and the route numbers to be noted
Thank you for you help

Try the attached. It works without a macro, rather with 5 random numbers that are re-calculated every time a cell is changed.

Based on that, there is conditional formatting applied to the yellow cells, so they turn red when their route number is in the list of the 5 random numbers.

You will need to install and activate the Analysis ToolPak for the randbetween() function to work. See here http://office.microsoft.com/en-us/ex...092301033.aspx

Slight mod to Teylyn's formula in column-J - just using the COUNTA function to set the upper limit for the RANDBEWTEEN function. This will limit the upper boundary to the number of routes in the table.

Note: the count is adjusted to compensate for header/blanks rows per the sample workbook. You may need to adjust the formula for this if the real workbook is different.

=RANDBETWEEN(1,COUNTA(B:B)-3)

Thanks for your help and pointing me in the right direction guys,

My fault for not being totaly clear in my original post. Each time I input a time and move to another cell the random numbers change, including highlighting routes that have previously departed.

what I need is a way for a route number cell to randomly go red as we insert the time when a driver books out

Once you enter a value in depart time, it goes red (or not). It that alone sufficient, or does it need to stay red as other cells are filled?

Try this formula for the conditional format. It will look for a number entry and well as match the route number.

=AND(ISNUMBER(D4),NOT(ISNA(MATCH(B4,$J$4:$J$8,0))))

There is a possibility that you won't get five matches, but if there are time values in the cells then just press the F9 key to force a new set of random numbers to generate different matches.

James,

The cell doesn't need to stay red,,,,,,we need to randomly search 5 vehicles from the list as the drivers book out,,,,, what I need is 5 route cells to turn red randomly as the depart time is inserted

That's what my suggestion does. I'm not sure I understand what's not working for you.

T, I think James wants it to change (or not) as a value is entered in the depart time column, not en masse.

I'd like to go on record as saying that this seems like an unnecessarily complex solution, but I'm not seeing anything simpler.

Clearing one or more cells in the Depart column will clear the corresponding Search column. Other than clearing, you can only change one cell in the Depart column at a time. When they are all filled in, "Search" will have appeared exactly five times (the constant nSrch in the code, which you can change as desired).

The table is dynamic, and will adapt to varying numbers of rows according to the last number in the Start Time column. Do Insert > Name > Define to see (or Name Manager in Excel 2007).

The code below is in the sheet module in the attachment.

Please Login or Register  to view this content.

Hi Guys

Thank you all for your help,,

shg

this works works exactly as I need it,,,,, your reading of what I need is correct

teylyn

the problem with the 'randbetween', it changed all the routes(as shg said,'en masse') to be searched each time a driver booked out and a new cell was selected

your help and time is very much appreciated. I apologise if I wasn't exactly clear in my original post

Well, you could just ignore the color changes and only search those vehicles that are marked red after the last time is filled in. But I can see how the constant change of red to normal can be distracting.

Another possibility might be to turn off automatic calculation, fill in all your values, then hit F9 to see 5 rows light up.

cheers

You want to keep the user from clearing the word "Search", or keep him/her from clearing the departure time once entered (which also clears "Search")

Try this.

The sheet password is Bob. Change that in the code as desired.

Excel security is for convenience in honest people avoiding mistakes, not miscreants trying to break things. Sheet passwords are laughably easy to break. In addition, anyone could look in the code to see the password. You could circumvent that by putting a password on the VBA project, but they could still get the sheet password.

Keep that in mind.