Link to home
Start Free TrialLog in
Avatar of DarrenJackson
DarrenJacksonFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel vba question

Hi Guys,

I am trying to get this working in excel vb

If Application.WorksheetFunction.SumIf(Sheet2.Range("$A$2:$A$5000"), Sheet2.Range("$A$2:$A$5000"), Sheet2.Range("$I$2:$I$5000")) = Round(Sheet2.Cells(r, "F") * 52 / 12, 2) Then
             Sheet2.Cells(r, "I").Interior.ColorIndex = xlNone
        Else
            Sheet2.Cells(r, "I").Interior.ColorIndex = 3
        End If

it doesn't error but doesn't do as I'm expecting

it is part of a bigger piece of code and loops through the rows and I want it to run the SUMIF statement and if it matches as per the above script then don't colour the cell but if it doesn't then change cell to RED

any ideas where I am going wrong

Regards
Avatar of Rgonzo1971
Rgonzo1971
HI,

The range and the criteria of the SUMIF is the same is that right?

Regards
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Norie
Norie
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DarrenJackson
DarrenJackson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Guys thank you all for commenting I have checked over the solutions and RGonzo1971 has the solution. It appears I was

incorrectly formulating the sum if

from
If Application.WorksheetFunction.SumIf(Sheet2.Range("$A$2:$A$5000"), Sheet2.Range("$A$2:$A$5000"), Sheet2.Range("$I$2:$I$5000")) = Round(Sheet2.Cells(r, "F") * 52 / 12, 2) Then

to this
If Application.WorksheetFunction.SumIf(Sheet2.Range("$A$2:$A$5000"), Sheet2.Cells(r, "A"), Sheet2.Range("$I$2:$I$5000")) = Round(Sheet2.Cells(r, "F") * 52 / 12, 2) Then


This now works as I need

Thank you all for helping
Avatar of DarrenJackson
DarrenJackson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks for helping