VB in Excel

DEWEY84

New Member
Joined
Mar 25, 2011
Messages
3
Hi,

I have created a work sheet that will be used to show the status of orders. I have used conditional formatting for three of the status colours.

Amber - Issues
Green - On Going
Grey - Completed

When a staus is used from the drop down it will colour the cells within that particular row as above.

In column A all of the cells contain a target date. I need some code that basically highlights the cell in column A if the taget date is older than the current day - where the status of the row is "on-going - Green" or has "issues- Amber". I obviously don't want to include "completed" - grey rows.

Im new to VB so am struggling - I have looked around but can not find an answer. I also need this to run automaticly - when a new order is added to the sheet.

Please help.

Thanks.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Couldn't you just do that with a formula rather than code?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
Hi and welcome.

You could do this with your Conditional Formatting (CF) formulas.

Say column B is where you have your Status dropdown lists.
You prbabbly have a a CF formula something like...
Condition1
Formula is =$B1="Issues"
Format: Amber


If you want to Not highlight (exclude) rows where the Dates in column A are past Today's date, change the CF formula to something like this...
Formula is =AND($B1="Issues", $A1>=TODAY())
 

DEWEY84

New Member
Joined
Mar 25, 2011
Messages
3
Thanks for the quick replies - With the CF would I have used the three options for each row. I have used three colours. i want to use another colour in the A column (Red) so that just the cell in Column A turns red when the date = older than current day. I want the rest of the row to stay the coolour of the status. - Green - Amber.

Unless the status is completed as target date would not be of any importance.

CAn I do this with CF? With the statments I am saying:

If order has a status of "On going" highlight row green - If "Target date" is < current date highlight the cell in column A red. Leave the rest of the row green.

Same for issues just different colour.

Sorry if I am not making sense - This is all new. All your help is appreciated. Thanks
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278

ADVERTISEMENT

CAn I do this with CF? With the statments I am saying:

If order has a status of "On going" highlight row green - If "Target date" is < current date highlight the cell in column A red. Leave the rest of the row green.

Can't have two colors for one condition. Sorry


I understand what you are asking for. I just want to make sure you really need VBA to do this. Before using VBA, two more things to try first...

Do you have any rows in all your data that are not Amber, Green, or Grey? If no, you cold make the background color say grey for ALL your data and remove the CF for Grey - Completed. Then use that 3rd CF in column A to test the Date.

Alternative to that, in just column A you could have no Amber, Green, Grey CFs. Leave it white or some other default color. The use One CF in column A to test the Date. Would that work for you?

If no to both, I'll post some VBA code for you. Will need to know what cells have the Drop down lists?
 
Last edited:

DEWEY84

New Member
Joined
Mar 25, 2011
Messages
3
Thanks for your response. I need the whole row to be the same colour I am affraid. All of the rows will be green - amber or grey with no blanks.

The target date that needs to hoighlight red ifs older than current date is in column A. The overall order status is in Column "H".

The hard thing I guess is to ensure that if status is set to completed - grey - the date highlight will not be required.

I have different tabs for different orders as well. Though the setup is exactly the same on each sheet. So I guess this would need to work for the workbook not just one sheet.

Your help with this is much appreciated. Thanks for your time.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
All of the rows will be green - amber or grey with no blanks.

  • Set all your cells with a default background color of grey.
  • In Columns B and to the right, put two CF formulas for just Green and Amber. If the condition is not green or amber, then it will default to grey.
  • In column A use three CF formulas for Red, Green, and Amber. Red should be the 1st condition because it has priority.

CF Fomula for column A (Red)
Select column A
Condition1:
Formula is =AND(A1< TODAY(), H1 < > "Completed")
Format: Red
 

Watch MrExcel Video

Forum statistics

Threads
1,109,383
Messages
5,528,385
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top