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.

 

[Legacy 68668]

R6C5

Cells(6, 5)
?

 

[ninjagrover]

R6C5

Cells(6, 5)
?

changing R6C5 to Cells(6, 5) produces a Runtime error: 13, Type mismatch.

E6 has the value that I want to compare the minus to...

 

[mikerickson]

If Cells(i, Cval) - Cells(i, Cval + 1) > Range("E6").Value Then

Conditional Formatting could be set to a formula. Selecting a cell in row 10 and entering a CF formula like
=($E$6 < $A10-$B10)

 

[Legacy 68668]

What value in E6 ?

 

[Legacy 68668]

If Cells(i, Cval) - Cells(i, Cval + 1) > Range("E6").Value Then

Shouldn't make any difference.

 

[Legacy 68668]

ninjagrover

Check out all these three cell values.

You can not compare numeric value with string value.

 

[ninjagrover]

What value in E6 ?

100,000

The two columns are comparing budget v expenses.

So if Budget-Expenses is greater than 100,000, account is underspent so turn green.

Once I get the first part working, I'll add another condition that turns red if overspent (ie less than 100,000).

To mikerickson, I do have a working condition format in a macro (i mentioned this in 1st post, If you want I'll post the code), but when you go and copy/paste elsewhere, the colour from the format doesn't paste properly as per the condition. The boss I'm making it for isn't excel savvy and doesn't like the "fixed" colours of conditional formats -.-...

 

[ninjagrover]

ninjagrover

Check out all these three cell values.

You can not compare numeric value with string value.

Values are numbers...


<TABLE style="WIDTH: 175pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=233 border=0 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 81pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=108 height=17 x:num>100000</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 94pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=125></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17>Full Year Budget</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver">Full Year Exp 2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>292417</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="376826.1333333333">376826</TD></TR></TBODY></TABLE>

Cval is the result of a formula (=VLOOKUP($D$3,Sheet2!$A$1:$B$26,2,FALSE)) and D3 is the column letter, which will be entered by the user of this form, it just returns the column number from a list of a-z in column a, and 1-26 in column b on sheet 2, this is because the two columns being compared can change.

 

[Legacy 68668]

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