View Full Version : Solved: Format cells in noncontiguous selected rows

12-20-2006, 02:56 PM
I'm working on a spreadsheet which will be protected and used by many people. I need a macro that will highlight cells in columns A thru E for each row they select.

They might select one or many rows before running the macro, and the problem I'm having is getting this to work with noncontiguous selections. :banghead:

I also need it to check first to make sure at least one entire row is selected, otherwise it highlights the active cell and four cells to the right.

Here's what I'm using. It works for contiguous rows, but I'm sure it could be improved on. Any advice would be appreciated.

Dim RowCount As Integer
For RowCount = 1 To Selection.Rows.Count
Selection.Cells(RowCount, 1).Interior.ColorIndex = 6
Selection.Cells(RowCount, 2).Interior.ColorIndex = 6
Selection.Cells(RowCount, 3).Interior.ColorIndex = 6
Selection.Cells(RowCount, 4).Interior.ColorIndex = 6
Selection.Cells(RowCount, 5).Interior.ColorIndex = 6
Next RowCount

I've searched, but can't find a solution to this one. I hope someone can help.


12-20-2006, 04:08 PM
Is this what you need?

Sub Highlight()
Dim c As Range
For Each c In Application.Intersect(Selection, Range("A:A"))
c.Resize(, 5).Interior.ColorIndex = 6
Next c
End Sub


12-21-2006, 06:04 AM
That works perfectly, thank you so much!