If in VBA

[ninjagrover]

Hi All.

Been teaching myself VBA and I've gotten stuck, and no matter how I search I can't seem to find an answer...

Trying to set up a condition is vba where if a cell value minus another cell value is greater than a static value (elsewhere in the sheet), then to turn the font green.

Code so far:

    Dim Cval As Integer
    Cval = Cells(3, 6).Value
    FinalRow = Cells(65536, Cval).End(xlUp).Row
    For i = 10 To FinalRow
        If Cells(i, Cval) - Cells(i, Cval + 1) > R6C5 Then
            With Cells
            .Font.ColorIndex = 10
            .Font.Bold = True
            End With
        End If
    Next i
End Sub

I can't work out the right sintax to get a if condition to work correctly.

I have gotten this to work with conditional format, but if you want to copy/paste elsewhere, it doesn't paste the format result correctly...

Any advice would be appreciated.

 

[ninjagrover]

Then I got it

You can't add/deduct from String value.

Cval + 1

change to

If Cells(i, Cval) - Cells(i, Cval).Offset(,1) > Cells(6, 5) Then

Ahhh, interesting.

However code is still returning a run time '13' type mismatch.

I even changed the colums to the actual column number, still no go õ.o

    Dim Cval As Integer
    Cval = Cells(3, 6).Value
    FinalRow = Cells(65536, Cval).End(xlUp).Row
    For i = 10 To FinalRow
        If Cells(i, 5) - Cells(i, 5).Offset(, 1) > Cells(6, 5) Then
            With Cells
            .Font.ColorIndex = 10
            .Font.Bold = True
            End With
        End If
    Next i
End Sub

 

[Legacy 68668]

OK

Are you running the code for the ActiveSheet ?
Or is it suppose to run on the other sheet?

 

[ninjagrover]

Active sheet.

Incendentally, here is the code of the conditional format that works with a matrix of 7 columns long.

    FinalRow = Cells(65536, 6).End(xlUp).Row
    With Range("F6:F" & FinalRow)
        .FormatConditions.Delete
        .FormatConditions.Add xlExpression, Formula1:="=(RC-RC[1])>R2C7=TRUE"
        .FormatConditions(1).Font.ColorIndex = 50
        .FormatConditions(1).Font.Bold = True
        With Range("G6:G" & FinalRow)
            .FormatConditions.Delete
            .FormatConditions.Add xlExpression, Formula1:="=(RC-RC[-1])>R3C7=TRUE"
            .FormatConditions(1).Font.ColorIndex = 46
            .FormatConditions(1).Font.Bold = True
        End With
    End With
End Sub

 

[Legacy 68668]

Ahhh

I think you said Cells(3, 6).Value is the result from VLookup and wuould be a,b,c,d,e...

Then Cval can't be an integer, so type mismatch

Dim Cval As Integer

 

[ninjagrover]

Ahhh

I think you said Cells(3, 6).Value is the result from VLookup and wuould be a,b,c,d,e...

Then Cval can't be an integer, so type mismatch

Dim Cval As Integer

No, the results from the Vlookup is a number. ie if a=1, b=2 so on up to z=26...still means same problem?

Even when I replace the value of F3 with a value (5), the code still runs into the same error message (with the if line highlighted in yellow btw).

 

[Legacy 68668]

The value reruned from VLookup formula is String, unless you multiply/add/devide by 1.

Just try As Variant

 

[ninjagrover]

The value reruned from VLookup formula is String, unless you multiply/add/devide by 1.

Just try As Variant

Changed to As Variant. Same error msg. If line highlighted. Even when I replace Cvar with the actual column, it still refuses to work.

Dim Cval As Variant
Cval = Cells(3, 6).Value
FinalRow = Cells(65536, 5).End(xlUp).Row
For i = 10 To FinalRow
If Cells(i, 5) - Cells(i, 5).Offset(, 1) > Cells(6, 5) Then
With Cells
.Font.ColorIndex = 10
.Font.Bold = True
End With
End If
Next i
End Sub

Gah!! Sorry for all the hassle. And here I though it would be easy to turn from conditional format to actual colour change!!

 

[Legacy 68668]

That's odd
How about

Dim Cval As Variant, r As Range
Cval = 5
For Each r In Range(cells(10, Cval), Cells(Rows.Count, Cval).End(xlUp))
   If r.Value - r.Offset(, 1).Value > Cells(6, 5).Value Then
        With Cells
            .Font.ColorIndex = 10
            .Font.Bold = True
        End With
    End If
Next
End Sub

Gah!! Sorry for all the hassle. And here I though it would be easy to turn from conditional format to actual colour change!!

Then your code is coloring the font of entire sheet, doesn't make sense

 

[ninjagrover]

Hi Seiya.

Thanks for all your help yesterday. It's greatly appreciated to a newbie like me.

After some more work I managed to get it working as I want it to.

Involved moving the vlookup to the macro and pastespecialling to turn it into a number then usining As Integer for Cval.

Again, thanks for all your help, wasn't aware about how strings need to be treated so it's been a great learning experience.

Code is as follows:

    [F3].Value = "=Vlookup(R3C4,Sheet2!R1C1:R26C2,2,False)"
    Range("F3").Copy
    Range("F3").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Dim Cval As Integer
    Cval = Range("F3").Value
    FinalRow = Cells(65536, Cval).End(xlUp).Row
    For i = 11 To FinalRow
    Cells(i, Cval).Offset(, 2).Value = "=RC[-2]-RC[-1]"
        If Cells(i, Cval).Offset(, 2) > Cells(6, 5) Then
            With Cells(i, Cval)
            .Font.ColorIndex = 10
            .Font.Bold = True
            End With
        End If
    Cells(i, Cval).Offset(, 2).ClearContents
    Next i
End Sub

Maybe in the future I'll know how to do the whole compairson in one line insead of actually putting a formula into the sheet:

Cells(i, Cval).Offset(, 2).Value = "=RC[-2]-RC[-1]"

and then doing the comparison from that. But for the moment I'm just glad I got it to work!!