help with cell reference...
 

Hi

seems to be a simple question, just not so familiar with excel vba.

I am looping through column b (the loop happens after another condition is
met, i will leave this out of my snippet) so my range is column b and a 5
rows. i would like to format cells in column d & e if a codition is met. how
do i refer to column d & e if my range only has column b. i am pasting a
snippet of the code.

thanks,

sam


Code:
Set MyCell = .Range(.Cells(intRowCounter + 1, 2),
..Cells(intRowCounter + 5, 2))
For Each rRange In MyCell
If Trim(rRange) = "" Then
' this finds the total and colors it pink
' this is where i need some help
'rRange(.Cells(1, 4), .Cells(1,
5)).Interior.ColorIndex = 7
'rRange(.Cells(1, 4), .Cells(1, 5)).BorderAround
Weight:=xlMedium
Exit For
End If
Next rRange

help with cell reference...
 

Hi

I figured it out. i am just trying to minimize the code. is it posible to
combine these two lines:

rRange.Offset(0, 2).Interior.ColorIndex = 7
rRange.Offset(0, 3).Interior.ColorIndex = 7

Thanks,

Sam

"SAm" wrote:

Hi

seems to be a simple question, just not so familiar with excel vba.

I am looping through column b (the loop happens after another condition is
met, i will leave this out of my snippet) so my range is column b and a 5
rows. i would like to format cells in column d & e if a codition is met. how
do i refer to column d & e if my range only has column b. i am pasting a
snippet of the code.

thanks,

sam


Code:
Set MyCell = .Range(.Cells(intRowCounter + 1, 2),
.Cells(intRowCounter + 5, 2))
For Each rRange In MyCell
If Trim(rRange) = "" Then
' this finds the total and colors it pink
' this is where i need some help
'rRange(.Cells(1, 4), .Cells(1,
5)).Interior.ColorIndex = 7
'rRange(.Cells(1, 4), .Cells(1, 5)).BorderAround
Weight:=xlMedium
Exit For
End If
Next rRange

help with cell reference...
 

Sam,

Try it like this

rRange.Offset(0, 2).Resize(1, 2).Interior.ColorIndex = 7


--
Hope that helps.

Vergel Adriano


"SAm" wrote:

Hi

I figured it out. i am just trying to minimize the code. is it posible to
combine these two lines:

rRange.Offset(0, 2).Interior.ColorIndex = 7
rRange.Offset(0, 3).Interior.ColorIndex = 7

Thanks,

Sam

"SAm" wrote:

Hi

seems to be a simple question, just not so familiar with excel vba.

I am looping through column b (the loop happens after another condition is
met, i will leave this out of my snippet) so my range is column b and a 5
rows. i would like to format cells in column d & e if a codition is met. how
do i refer to column d & e if my range only has column b. i am pasting a
snippet of the code.

thanks,

sam


Code:
Set MyCell = .Range(.Cells(intRowCounter + 1, 2),
.Cells(intRowCounter + 5, 2))
For Each rRange In MyCell
If Trim(rRange) = "" Then
' this finds the total and colors it pink
' this is where i need some help
'rRange(.Cells(1, 4), .Cells(1,
5)).Interior.ColorIndex = 7
'rRange(.Cells(1, 4), .Cells(1, 5)).BorderAround
Weight:=xlMedium
Exit For
End If
Next rRange