Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Conditional Formatting using Vlookup/AND function


Im trying to use conditonal formatting in a cell if that number in the cell is equal to a another cell in a list of numbers and if the adjacent cell is equal to certain text. the problem im having is that the list of numbers and the adjacent cell get sorted everyday so the reference cell for the adjacent cell cant stay the same.

for example. I want C2 to turn green if the value in C2 is equal to any cell in C13:C27 AND the adjacent cell to right is equal to "text".  it would be easy if the cells never move but it gets sorted based on column A "start date" 

attached is the workbook



Selected Answer

Please try this formula.


For easier reading and management I have named two ranges.

C13:C100 = Kits
C13:D100 = KitStatus

If you need more rows simply adjust the size in the Names Manage. No need to touch the formula. Similarly, I have set [A2] = "On Location". This is to avoid having the text in the formula. It looks to me that you already have a cell from where the validation list in column D takes this word. Best practice: use the same cell as a reference in your CF formula.

Edit 07 Aug 2018   ==================================

Before the formula can work for you the two named ranges must be set in your workbook. If you don't want to do that, replace the names in the formula with the hard range addresses.

I now attach a workbook where the formula actually works. I changedd C2 = 77 and the cell is white. Change it back to the original 3 and it will turn green.



i put that formula in conditional formatting for C2 and it didnt do anything?  im not sure i understand what you saying to do
bdalfrey23 (rep: 2) Aug 6, '18 at 9:06 am
Thank you!!!! that worked
bdalfrey23 (rep: 2) Aug 8, '18 at 4:02 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login