referencing $E2 in VBA

[dshafique]

Hi guys, so im having an issue figuring out something. I'm trying to write a code which changes the color of a cell depending on if its greater than or less than a different cell. as a formula, i would have done $E2, however, when i put $E2 in vba, all the cells then reference only E2, i want it to look at the cell in the corresponding row of Column E. I cant do RC- the number of columns because im working on a dynamic sheet that adds columns each month. this is what i have, if it helps

Range("TableAll[[#Headers],[Fiscal YTD]]").Select
    ActiveCell.Offset(1, 1).Select
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    
    Dim cl As Range
    For Each cl In Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
    If cl >= "" Then
       cl.Interior.Color = 5287936
    End If
    Next cl

 

[Norie]

What formula are you trying to use and where are you trying to use it?

 

[Jonmo1]

Try

If Cells(cl.Row, "E") >= 0 Then

 

[dshafique]

my bad guys, it seems like i accidentally deleted the condition, here it is again

Range("TableAll[[#Headers],[Fiscal YTD]]").Select
ActiveCell.Offset(1, 1).Select
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select

Dim cl As Range
For Each cl In Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
If cl >= "$E2" Then
cl.Interior.Color = 5287936
End If
Next cl

 

[Jonmo1]

ok, that makes more sense.

Try

If cl >= cells(cl.row, "E") Then

 

[dshafique]

Thanks it works perfectly! you're a life saver!

 

[dshafique]

One more thing, how would i write it if i want it to be greater than or equal to? do i just put two equal signs?

 

[Jonmo1]

The posted code IS using greater than or equal to.

 

[dshafique]

ok thanks, i thought the equals sign was for the cell, like in formulas

 

[Jonmo1]

You're welcome.