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 - Need Formula Help

0

Hello helpers!

I need help with a formula and can not seem to get it to work. I think i need to build in and IFERROR formula and can not figure it out.

I have two colums that require a conditional format.

Column M - This is either "Blank" or a capital "X"

Column N - This is a VLookup result so there are "#N/A", The other results are "Completed" "Pending" "Ready" "Dispatched" "Canceled"

The result I am looking for is to highlight the row when:

Column M is "Blank" OR Column N Does not equall "Completed"

Thanks!

excelformatting
MS Office Version: OneDrive
posted by: PleaseHelpExcel on Wed Dec 11, 2019 at 8:49 am
Answer
Discuss

Answers

0

The formula to use for the CF is:-

=OR($M1="",$N1<>"completed")

It will fail if there is an error in either referred cell. Therefore you mustn't allow VLOOKUP to return #N/A. You are right to use the IFERROR() function for this purpose. Construct your VLOOKUP like this:-

=IFERROR(VLOOKUP([your formula],"N/A")

The "N/A" entered by the function is different from an error. Your CF formula will interpret it as <> completed.

posted by: Variatus (rep: 4889) on Wed Dec 11, 2019 at 9:05 pm
Discuss


Answer the Question

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