William has attended:
Excel Advanced - Formulas & Functions course
Colour look-up function or macro
Hi,
I am looking for a lookup function but I'm not sure if it is possible. I work with an excel spreadsheet where each day I input data into a new row. I always work with only 6 columns. Occassionally when necessary, I highlight a cell in the new row a colour (always purple) as I have I have completed a certain task. In column A I always have the current date. What I want excel to do, is on the top row of my spreadsheet, I want excel to find the lowest (or highest row number) ranked cell that is highlighted purple, and then give me the date located in column A for that row. For example, Today, I will colour cell C144 purple, then I want cell C1 to tell me what the date is in A144. Then in 10 days, I'll colour cell C188 purple, I'll want C188 to update to give me the date from A188. Is this possible? Thank you.
RE: Colour look-up function or macro
Sorry, in that example it should say 10 days, I'll colour cell C188 purple, I'll want C1 to update
RE: Colour look-up function or macro
Hi William,
Thank you for the forum question.
Everything (almost) is possible in Excel.
What you want can only be done writing a VBA code. I do not know if you have any VBA knowledge but it cannot be done in few lines.
You can filter the data by colour and get the last record with purple at the top but this will not give you the information you want in C1.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Colour look-up function or macro
Thank you for your response Jens.
I do not have any knowledge of VBA code. How close could a VBA code get to my desired effect?
Also, is there a way to get excel to pull the last record with purple to C1? That could also be useful.
Thanks once again.
Will
RE: Colour look-up function or macro
Hi Will,
Please find attached workbook.
I have created the VBA macro in the workbook, which will do what you want but without VBA knowledge it can be difficult to amend and understand the code.
If you in with the workbook open press ALT F11 the visual basic editor will open and you can find the code in here if you navigate to the module in the workbook. You will find a function I have created to find the colour index number. The function is used in E1 in sheet 1 (I have used colour index number 47 in the example). In the module under the function you will find the code which doing the job.If you use the same purple colour as me you can add the colour to any in the table and press the run button and you can see the date in C1.
VBA is very powerful and can save us a lot of time working with Excel but I can suggest that you have a look at our VBA course which will give you the necessary tools if you want to take this road.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
