Matching Date

**VKu** · 05-01-2015, 04:41 PM

Hi, could any of you beautiful people out there please help write a VBA Macro for me. I’m very new to VBA programming and need your help.

I’ve a big set of data, with columns containing date and price of different products.

First, I’d like to
- Highlight the entire column with dark grey color if cells in row 4 contains the word Timestamp
- Highlight the entire column with light orange color if cells in row 4 contains the word Trade Close

Second
- Match the date so that all columns with a specific date is in the same row, that is there will be only one date per row of the entire data set, move the date so that it matches each other and also move its corresponding prices too.

Please see attached excel file.

I thank you in advance of your help. Your kindness is much appreciated.

Vku

**FDibbins** · 05-01-2015, 05:48 PM

For the coloring part, you dont need VBA, try this...

1. highlight the range you want to apply the conditional formatting to (B3:LI122 in your sample)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =B$4="TimeStamp" Format fill Grey

For the sorting, you will need VBA - not my strong side

**JasperD** · 05-01-2015, 06:20 PM

I agree with Ford that for the coloring, conditional formatting is the way to go.

For the sorting:
What do you want to happen when a column doesnt have a certain date.
For example, in your file, from column J, the date 2/4/2011 is missing (among others).

**skywriter** · 05-01-2015, 06:33 PM

I'm not so sure I agree. That is a lot of cells you are concerned with coloring and CF is considered volatile.

Decision Models

"Conditional Formats are volatile.

Because conditional formats need to be evaluated at each calculation any formulae used in a conditional format is effectively volatile. Actually conditional formats seem to be super-volatile: they are evaluated each time the cell that contains them is repainted on the screen, even in Manual calculation mode, although VBA functions used in conditional formats will not trigger breakpoints when executed by the repaint."

**skywriter** · 05-01-2015, 06:57 PM

Here's a macro you can run as needed to color the columns.
To install it you can click Alt + F11 and in the window that comes up choose insert from the drop down menus and choose module.
Then paste the code into the window. Save the workbook as a .xlsm style file and then run the macro.

Please Login or Register  to view this content.

**FDibbins** · 05-01-2015, 07:01 PM

skywriter, I agree that excessive use of CD can start to slow your file down. If the data range is far larger than the sample, then VBA would be the go. However, you have included both into 1 macro, so that would be a better option

**VKu** · 05-02-2015, 05:37 AM

Thanks everyone for the post, the VBA macro to highlight the cells work perfectly.

If anyone could also help me out with the sorting and matching the dates I would highly appreciated it.
Please see the attached file in the post.

Thanks in advance.

Vku

**skywriter** · 05-02-2015, 12:16 PM

Originally Posted by FDibbins

skywriter, I agree that excessive use of CD can start to slow your file down. If the data range is far larger than the sample, then VBA would be the go. However, you have included both into 1 macro, so that would be a better option

Sorry I don't understand the reply.
I have included both "what" into 1 macro?

**FDibbins** · 05-02-2015, 02:08 PM

Originally Posted by skywriter

Sorry I don't understand the reply.
I have included both "what" into 1 macro?

Sorry, I thought your macro did the CF and the sorting for them

Matching Date

LinkBack

Thread Tools

Rate This Thread

Display

Matching Date

Re: Matching Date

Re: Matching Date

Re: Matching Date

Re: Matching Date

Re: Matching Date

Re: Matching Date

Re: Matching Date

Re: Matching Date

Thread Information

Users Browsing this Thread

Similar Threads

Sum ifs - Based on Matching Date (exact date not range)

Add date criteria matching order date and sales date

[SOLVED] Fill End Date with Start date of the next Matching Entry

[SOLVED] Finance Question- matching sales value date with that of amount received date

Indexing and matching data from date range and matching

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions