Break down a Target range?

[metaldogo70]

I am planning on using the Worksheet_Change event subroutine to apply cell formatting to a changed cell. But, the Target from the Worksheet_Change subroutine can be a range of more than one cell. In order to apply the formatting to each cell that has changed, I will need to break down the Target value into starting row and column and ending row and column. How do I do this?

Thanks!
JJ

[Zack Barresse]

Hi JJ, welcome to the board!!!

Dim sRow as long, eRow as long
Dim sCol as long, eCol as long

sRow = Target(1).Row
eRow = Target(Target.Rows.Count).Row

sCol = Target(1).Column
eCol = Target(Target.Columns.Count).Column

You could also just loop through the range ...

Private Sub Worksheet_Change(ByVal Target as range)
Dim c as range
If Target.Cells.Count > 1 then
For each c in Target
'...
Next c
Else
'.. single cell
End if
End Sub

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target as range)
Dim c as range
If Target.Cells.Count > 1 then
For each c in Target
'...
Next c
Else
'.. single cell
End if
End Sub

You can iterate through a single cell range as well, so no need to test


Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target
'...
Next c
End Sub

[Norie]

JJ

Why do you need to break down the target range?

Can't you just apply whatever formatting to the whole range at the same time?

Target.Interior.ColorIndex = 3

[Cyberdude]

If you want to go to the trouble of looking for it, I have an entry in the Knowledge Base named "Parse a Range Address Into Column and Row Components". The name of the macro it describes is (what else?) ParseRange. I use it a lot. Copy it and put it into your Personal.xls for general use.

OK, here's how you get to it: Click on "Go Here"
Go Here