Target.Column problems

**james.benham** · 07-02-2009, 05:19 AM

Im having some trouble with Target.Column / Target.Row syntax.

The code below is a snippet of a conditional formatting problem that I posted about yesterday. I have been working on it since but cant seem to get it too work.

When the if statements read:

If Target >= (Range("H" & Target.Row).Value + Range("O15").Value + 2) Then icolor = 45

rather than

If Target >= (Range("H" & Target.Row).Value + Range(Target.Column & "15").Value + 2) Then icolor = 45

They work, but obviously this is only relevant for column O.

Any idea what I'm doing wrong??


Dim icolor As Integer
    If Not Intersect(Target, Range("o16:ao35")) Is Nothing Then
           
        If Target = "" Then icolor = 2
                            
        If Target >= (Range("H" & Target.Row).Value + Range(Target.Column & "15").Value + 2) Then icolor = 45
        
        If Target >= (Range("H" & Target.Row).Value + Range(Target.Column & "15").Value + 10) Then icolor = 38


        Target.Interior.ColorIndex = icolor

    End If

**DonkeyOte** · 07-02-2009, 05:39 AM

Target.Column will return the number of the column so assuming Target was in P:

Range(Target.Column & "15").Value

will translate as

Range("1615").Value

which is obviously not valid syntax... you should perhaps revert to Cells based approach (R1C1) given it is significantly more flexible than using Range