Recommend a method please! VBA / formatting

sanantonio

Board Regular
Joined
Oct 26, 2021
Messages
124
Office Version
  1. 365
Platform
  1. Windows
Hi,

So I have a base data field.

1682259321603.png


Then I need a vba code that will check whether any of these items (They will be in alternate columns D,G, etc. the length will vary but it wouldn't hurt if it had to search through any blank columns) are NOT on a status 9. This is determined by looking up into a second data field:

1682259464563.png


Then return a message box if it finds ANY item that is NOT on status 9, and then run a macro if all of them are on status 9.

And then extra, conditionally format any items that aren't on 9.

Any suggestions? I've been fumbling around for a while this weekend trying to figure the best way of doing this. Any help would be appreciated.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
This is an XLOOKUP ; with conditional formatting
determined by looking up into a second data field . . . conditionally format any items that aren't on 9.

ANY item that is NOT on status 9, and then run a macro
Two options: Run your macro and return items not with a 9 in message box or w/e, or proceed with base code when all are 9;
or use dynamic results table/power query to see the items not status 9 and run macro when dynamic table is empty.
 
Upvote 0
Hey, thanks for coming back to me. Did you mean to attach an example of an xlookup with conditional formatting?
 
Upvote 0
This is what I meant an example based on your pics above:
Book1
ABCD
1ItemQuantityItemQuantity
210000011234561
310000129876542
BASE DATA FIELDS
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B3Expression=NOT(9=VLOOKUP($A2,'LOOKUP ITEMS'!$E:$R,14,FALSE))textNO
C2:D3Expression=NOT(9=VLOOKUP($C2,'LOOKUP ITEMS'!$E:$R,14,FALSE))textNO


Book1
ER
1ITEMITEM STATUS
21000009
310000110
41234569
598765410
LOOKUP ITEMS
 
Upvote 1
Solution
You will also need based on the data being multi columns a rule for every column pair like I have shown
 
Upvote 0
If you don't need to see the result before running your macro, you could add it to the macro when it masterfully :) loops through the item and qty fields.
 
Upvote 0
Ideally I'm trying to build it into the macro so it runs the check and highlights anything on status 9 prior to it running
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top