checking for empty values and using offset

[ritz]

Hey Guys,
I'm trying to write an if and statement in VBA to reference columns 15 and 16 (using offset) and tell them that if they are both empty that the interior color needs to stay empty in both.
here is what i have but it isn't working:

Please Login or Register  to view this content.

Sub mondaytry()
Range("b4:b341").Name = "dashboard"
Range("I1").Name = "today"
For Each cell In Range("dashboard")
If cell.Offset(0, 16) = Range("today") Then cell.Offset(0, 16).Interior.ColorIndex = 8
Next

For Each cell In Range("dashboard")
If IsEmpty(cell.Offset(0, 15)) And IsEmpty(cell.offest(0, 16)) Then
cell.Offset(0, 15).Interior.ColorIndex = 9
cell.Offset(0, 16).Interior.ColorIndex = 9
End If
Next

End Sub

Please Login or Register  to view this content.

Any Thoughts as to how i can fix it?
THanks,
Rytis

[VBA Noob]

ritz,

Please read forum rules below and then wrap your code

VBA Noob

[Leith Ross]

Hello Ritz,

The Offset method of Range object starts at the refrence cell. Rows move down with postive values and move up with a negative values. Columns move left with positive values and move to the right with negatve values.

You are start in column "B4". Offset(0, 1) would then reference cell "C4". Your code references Offset(0, 16) or cell "R4". If you want to reference cell "O4" then the offset needs to be Offset(0, 13).

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Leith Ross]

Hello Ritz,

Your first post and second post are different, so I am not sure what you really want to do. If the cell in column 16 is the controlling value that determines if column 15's color changes, your code would be this...

Please Login or Register  to view this content.

Sincerely,
Leith Ross