Excel macro woes

Woden · 22 Dec 2014 at 21:02

Just a simple one I guess.

I am trying to write a macro that will change the background of the current row, and increase the value of a cell by 1. I have hit a stumbling block though as my code (below) actually colours the row a few lines down. And If it's not column A selected, the coloured cells will be shifted over.

What I want is to be able to select say any cell row 7, then A7:K7 will turn colour.

Code:

ActiveCell.Range("A" & ActiveCell.Row(), "K" & ActiveCell.Row()).Interior.ColorIndex = 8

Thanks guys.

james.miller · 23 Dec 2014 at 12:31

Activecell.range is confusing it, change it to ActiveSheet.Range

GAM8LE · 23 Dec 2014 at 13:24

This works:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target.Cells(1)
If .Row <> 7 Then Exit Sub
Range("A7:K7").Interior.ColorIndex = 8
End With
End Sub

Any cell in row 7 that gets selected colours your A7:K7 range.

just whack this under the sheet you want the change to happen on

Woden · 24 Dec 2014 at 00:59

Was actually easier than I thought. I ended up with:

Code:

Range("A" & ActiveCell.Row & ":L" & ActiveCell.Row).Interior.ColorIndex = 8