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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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,450
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,450

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,450
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,512
Members
430,436
Latest member
fefenouil

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
Top