0 votes
in VBA by Beginner (2 points)
I have a file that gives me forcasted stock position per month. This is based on a lead time vs my forecast

So for example a supplier has a lead time of 4 months, so i need to know if in the next 4 months if i will go into a negative stock position. After that it does not matter as the sytem will generte an order.

This would be easy but differant suppliers have differant lead times, so i would like to create a macro that highlights a negative cell based on a lead time. I have the lead time in a cell and have coverted this into a month value . I can then put this into my day end so when the planners arrive they can focus on the parts that will run out rather than all parts from a supplier.
by Super Expert (3k points)
Can you upload some screenshots of what you're looking for?
by Beginner (2 points)
edited by

Sorry for the late reply. Have been away


Below is what i am looking to do. I need to know if we will run out of stock before the next shipment is due. I have this information but would like to highlight this as it will be a more visual and focus the planner 


1 Answer

+1 vote
by Beginner (6 points)
use conditional format.

select all cells in the leadtime. create new format condtition. select "use formula to determine which cells to format".

on the Rules:

[firstcell in forecast] - [first cell in lead time] < 0
by Beginner (2 points)
Hi Thanks for the reply but my lead time is a month figure and not a monthy demand, so your formula does not work as i have to take into account projected reciepts

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.