VB in Excel

[DEWEY84]

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.

 

[Norie]

Couldn't you just do that with a formula rather than code?

 

[AlphaFrog]

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]

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]

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?

 

[DEWEY84]

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]

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