We help IT Professionals succeed at work.

Excel vba question

Last Modified: 2017-04-08
Guys,I am trying to get this piece of code working but I know its wrong but cant think how to fix it

What I want it to do is look at the cell in column D and compare the result of the formula which is a look up and if they match then to not colour the cell but if they don't then I want it to colour the cell.

Sub LoopFormula()

On Error GoTo myError

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For r = 3 To LastRow

If (Sheet2.Cells(r, "D")) = "=IF(D3=VLOOKUP(D3,Project!$A$2:$A$3000,1),VLOOKUP(D3,Project!$A$2:$A$3000,1))" Then Sheet2.Cells(r, "D").Interior.ColorIndex = xlNone
If (Sheet2.Cells(r, "D")) <> "=IF(D3=VLOOKUP(D3,Project!$A$2:$A$3000,1),VLOOKUP(D3,Project!$A$2:$A$3000,1))" Then Sheet2.Cells(r, "D").Interior.ColorIndex = 3


Exit Sub

MsgBox ("Error"), , "Error"

End Sub

Any help
Watch Question

Berkson WeinTech Freelancer

Sorry to start answering your question with a question, but I'm a bit perplexed as to why you're trying to use vba for this.

Is there a reason that you're not using conditional formatting to change the color of a cell based on its value?  If you use (corrected) vba code to do this comparison, it would be a one time thing.  That means that if a value in the worksheet changes, the coloring would not. With conditional formatting, the coloring is dynamic - if a value changes, so will the color.

Let me know what the end goal is here and I can help with either writing VBA code that will do a one time thing or conditional formatting that'll be dynamic.  Also, please attach a sample Excel file.


It's part of a bigger piece of code and your right the vba would only be able to validate once i have a button or checker as it's called so a person would make a change then use a button to validate it
I have a list of projects in a projects tab and would like the code to look at the cell in question then the vlookup to run and return either a match or not


If you still need a sample file I can arrange this but not tonight

Thanks for helping
Berkson WeinTech Freelancer
Unlock this solution and get a sample of our free trial.
(No credit card required)
Excel VBA Developer
Top Expert 2014
Unlock this solution and get a sample of our free trial.
(No credit card required)


Thank you both for helping me out I'll check out the code later on in the weekend as I'm.l now off to bed


Glenn this piece of code is brilliant love it its doing exactly what I need.

Thank you Thank you

Weinberk also thank you for taking the time to assist me.

Both of you again thank you

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.