Highlight cells/row with data only!

[Spellbound]

Hi all

Managed to find some code from previous posts (see below) which highlight the entire active row. However, is it possible to revamp the code so that it only highlights the cells in that row which contain data.

i.e. at present columns A to H contain data but additional columns are added each week. The row will always contain data (even if it is a zero value) starting with column A onwards.

The two examples of highlighting a row that I found are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Both appear to work the same but I would be interested to know if there is a benefit to using one particular code over the other.

Please note my knowledge of VBA is almost zilch, so I would need the someone to post the revised code in full.

TIA ...spellbound

[Leith Ross]

Hello SpellBound,

This macro will highlight only the cells in the row with data. You can change the variable Highlight to the color index number you want as a highlight color.

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

[Spellbound]

Hi Leith

Thanks for quick response.

I thought that I would give it a quick try before crashing!

Pasted the code as is but it is not clearing the fill colour in the last (previous) active row, when you move on to the next active row. So that the whole table is filling up with highlighted rows as you select the next rows.

Hope that makes sense ...spellbound

[Leith Ross]

Hello SpellBound,

Sorry about the misunderstanding. Just took a little tweak to make it behave the way you wanted.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Spellbound]

Hi Leith

That works great, thanks for your help.

spellbound

[Spellbound]

<<< bump >>>

Although I have marked the thread as SOLVED, whilst making alterations to my workbook, I found that the code is affecting things like copying/pasting/formatting within that worksheet.

e.g. if I try to copy a cell from A6 to A7, nothing happens, as if the paste function has been negated.

If I try to copy the formula from the formula bar, it pastes in the same cell reference. In other words $A6 does not change to $A7, it stays as $A6; plus it will not paste down more than one cell.

The only way that I could find of pasting the formula was to drag the first cell down to the last one. Whilst this "worked" it left most of the column not row(s) highlighted in yellow and destroyed the border formatting that I had in place.

I have now put the worksheet back to the way it was but I would still like to use this code if it can be corrected so that this does not happen again.

TIA ...spellbound

[Leith Ross]

Hello Spellbound,

The only way I know to fix this problem would be to manually activate it. The macro is run anytime a cell is selected. I would not be possible to adjust the code for all the instances that cause this event to occur. It could be added to the cell right click menu as an option. Sorry the news isn't better.

Sincerely,
Leith Ross

[Spellbound]

Hi Leith

Thanks for the update.

Whilst I would have preferred it just to highlight the relevent cells in a row, I think I will just go for one of the options, which highlight the entire row.

Manually activating the code is probably not a good idea, as I am bound to forget to turn it off when needed and others may also not realise how to use it in this way.

Mind you, not sure which of the 3 sets of code to use for highlighting the row but guess I can experiment with each one of them.

Thanks for your efforts ...spellbound