Excel VBA Macro to make highlighted cells be the same as the next non-highlighted cell above it

[ariesmethod]

This sounds complicated, but this is what I have to make a macro of for work. Listed below is a sample of the list of item codes that I have: ('h' stands for highlighted cell)


M101
S644 (h)
S753 (h)
M102
S531 (h)
etc....


I want to make the macro to give this end result using any method:


M101
M101 (h)
M101 (h)
M102
M102 (h)
etc....


Can someone link me a sample VBA macro that I can use to get this process done? Many thanks!

 

[asyed]

Which excel version are you using? If you are using 2007 or later, you could do it by using autofilter and a helper column:

1. Insert a helper column next to the list column
2. Apply autofilter over both the columns.
3. In the list column, Filter for all non - highlighted rows
4. Insert something random (the number 1, maybe) in the first visible cell of the helper column, copy the cell, select all visible cells in the helper column (select all cells next to the data then use shortcuts – “alt+;”), and paste.
5. Clear the filter and filter for blank cells in the helper column.
6. Insert "=(above cell reference)" in the first visible cell of the list column. For example, if first visible cell is A4 insert =A3 in that cell.
7. Copy this formula, select all visible cells in the column and paste special – formula.
8. Remove autofilter and you will have your result. You can then delete the helper column.

This is assuming that different highlights were used in the list column. If the highlight is of one colour only, you wouldn’t need the helper column. In that case, skip step 1, in step 3 filter for the highlighted rows, and skip steps 4 and 5, proceeds with other steps as is.

 

[mole999]

what routine creates the highlight ?