• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Apply Conditional formatting based on the value of a cell to the proceeding 6 cells

AlwenaB

New Member
Good Day all,

I have to explain the process of what I am trying to put in an excel workbook to simplify a process which takes up too many hours of my day. I also need to point out that I have only recently joined Chandoos VBA classes.

I inherited a process (paper) when I started working for a company in Zimbabwe 3 years ago. I have just been given permission to change it (simplify) the whole process.

The process start as follows:
1. ICO generated on four (4) Estates. (ICO = Inter Company Order). This means 4 different estates send their orders to me.

2. An ICO can request up to 8 different items which needs to be bought from 8 different suppliers.

3. From the ICO's (can receive up to 10 a day - very busy day) I need to see what needs to be ordered. Email 3 different suppliers for the same thing (need to produce for audit purposes 3 different quotes).

4. Once the quote was accepted the order from several ICO's need to be written in a book under the suppliers name for tracking purposes as a order is seldom complete.

5. Place the order by generating an order form for that supplier. On the order form the ICO number and Estate that ordered the specific item has to be indicated for reference sake as all 4 Estates can order the same item.

6. Upon receiving the order I need to check that the correct order was received. Now in the same book where the order was written for tracking purposes I have to write the date of when I received the order to show if there is anything still outstanding that I need to follow up.

What I have done so far:

I created a workbook in which my first sheet is a list of Suppliers with abbreviations that I use to identify them. The second sheet is ICO where I will enter the ICo to copy and paste to the respective sheets.

Then I created a sheet each for the 4 Estates.

Then the long list of sheets per suppliers that I deal with.

I searched the net for help and stumbled across Chandoo's site. I found a VBA code that assist me with getting the information into the separate sheets. (I did sign up for the excel, dashboards and VBA classes).

There is still a lot I want and need to do before I can start using the workbook. The problem I am facing now is that I want to on the supplier sheets strikethrough the orders when it is complete by checking the received date column. If there is a date in the column, I want cells a:h in the same row to be struck through so that I can see at a glance what is still outstanding. I also dont want the information to be deleted as I need to refer to it when need be.

I have tried to use that which I have learned already at Chandoo but I am failing. I can get part of it to work but its not moving to the next row to check and apply the condition.

I can get the Do While loop to work, the If statement to work but I dont know how to tell excel to apply the code to the next range.

I have uploaded the worksheet (NOT COMPLETED) as a sample.

If one of the capable Ninjas can have a look and let me know what I am doing wrong.

Thanks in advance.

AlwenaB
 

Attachments

  • OrdersTesting.xlsm
    43.3 KB · Views: 1
Hi Alwena,

Here are my initial thoughts.

1. You have Received Date column on Sheet AC,AD & AZ. Do you want to put the CF on these sheets?

2. This can be done with conventional CF, why you want to do this with VBA?

3. This is just a suggestion, instead of striking through if you fill the particular row with a color or change font color that way help you to filter the data.

4. Put some more data on these sheets (dummy data)

Regards,
 
Hi Somendra,

Thank you for replying.
Answers on your questions above.
1 That is correct. I need to put the CF in the sheets which is my suppliers starting with AC.

2 I wanted to try and implement everything I have learned so far on Chandoo and I thought I will create a icon on the ribbon which I can use every time I receive a delivery. I need something that will show me if something is completed but yet must be able to refer back to it as all deliveries is done at one point and then the Estates collect from there.

3 I looked at all the CF available but I couldn't figure out a way to do it, maybe because I wanted to use strike through as the format.

4 I have put some more dummy data in. I didn't run any of the codes yet.

Thanks again for trying to assist me.

Kind Regards
Alwena
 

Attachments

  • OrdersTesting.xlsm
    51.9 KB · Views: 1
Hi,

Ok will run the codes. Basically it copy and paste the information from the ICO sheet to the relevant sheets. There is 2 codes. One to copy and paste the information into the 3 estates, then the same information into the supliers for me to track. I have to keep all this information in the workbook for a fiancial year period.

That part I could do with help from a forum post on Chandoo.

Once the code is run and all the information is in the sheets as I want it then I want to apply the CF to the Suppliers starting at sheet AC, AD,AZ,BE etc.

I hope this helps. File uploaded now has the code run.

Thanks

Alwena
 

Attachments

  • OrdersTesting.xlsm
    54.3 KB · Views: 1
See this file, run the formatdata macro on any of the supplier sheet, to see the effect.

Regards,
 

Attachments

  • OrdersTesting (2).xlsm
    57.8 KB · Views: 2
You are the greatest! That is exactly what I wanted. Now for going though your code to see how to do it myself! Thanks a million! This is Great!

Much Appreciated!
 
Back
Top