Search : in
By :

Days lapsed conditional formatting VB

Last answer on Sep 30, 2009 2:28:03 pm BST Nick M, on Dec 3, 2008 7:38:59 am GMT 
 Report this message to moderators

Hello, I need a bit of help trying to format Excel

I need a cell to change colour in relation to the number of days expired to a given cell date:

for instance cell A1 has a date of 01/11/08 cell A1 should have a number of states depending on the number of days lapsed since that date.

So the states should be:

after 28 days cell turns Red
after 35 days cell turns Black

But.....in addition to this if the cell has a percentage number entered then the number will also change the colour:

So a percentage number of 80% or less the font colour needs to be Red but the background Green
If the Percentage colour is greater than 80% then the font colour needs to be Black and the background Green

Then...there's more... if a cell has a black percentage figure and a Green background, after 6 months the background needs to turn Orange.

Big ask I know and I'm not sure its possible. I have figured out how to do some of the requirements with conditional formatting but obviously this doesn't go far enough. I would be exceptionally grateful if anyone can figure this out.

Nick

Configuration: Windows XP
Internet Explorer 6.0

Best answers for « Days lapsed conditional formatting VB » in :
Conditional formating - apply on more cells Show
Unlimited Conditional Formatting Show Unlimited Conditional Formatting Introduction Principle Code Introduction The limitation of MFC (Conditional Formatting) to Excel version 2007 is less than 3. This is very short. Having seen many applications to push the limit, I am...
Remove blank cell from conditional formatting Show
Colouring cells on conditions ShowColouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a...
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Download Save Images Show
Download HP USB Disk Storage Format Tool ShowHP USB Disk Storage Format Tool is a small utility allowing to format a key USB or READER MP4. It also allows to make a key (Bootable). This software works with all keys and readers MP4 of the market. To make work this utility under Windows...
Download HDD Low Level Format Tool Show
Spreadsheets - Conditional expressions ShowWhat is a Conditional Structure? Conditional structures are instructions that allow to test if a condition is true or not. Conditional structures may be associated together. To successfully execute these tests using logical functions, spreadsheets...
MKV format (Matroska Video) ShowMKV format The MKV format (Matroska Video) is an entirely free video format. More precisely, it is a container (hence the name Matroska, in reference to the Russian dolls contained within another) making it possible to contain video (DivX, Xvid,...
Formatting - Formatting a hard drive ShowBefore trying to understand formatting, you first need to understand how a hard drive works. Many people do not distinguish low-level formatting (also called physical formatting) from high-level formatting (also called logical formatting). Even...
Ask your question Reply

1

forlift, on Dec 3, 2008 8:51:27 am GMT

Hey there,

just check the link below if you can find something useful there:

http://office.microsoft.com/en-us/excel/default.aspx

hope it helps you

   
Reply to forlift

2

Nick M, on Dec 3, 2008 9:56:22 am GMT

Thanks for a fast reply Forlift but that is exceptionally general and doesn't seem to cover the how to's of VB programming particularly for the novice like me.

I can get some of the criteria through conditional formatting but obviously I have more than 3 requirements and it wont allow for more than 3. I understand that VB can give you more formatting options.

Essentially the spreadsheet is a record of training for employees. As a training course is added the date that it is available determines a start time. The employee then has 30 days to complete it. Once completed a score is auto populated into the cell.

What I require is a colour (Amber) to signify a course is available or being taken, A colour (Red) to signify that the period for completion has expired. A colour (Green) to signify a course has been completed. Then once a course has been completed, the colour of the score (font - red =fail black = pass) then shows whether the required passmark was achieved. This then has to reset itself back to amber once 6 months has passed to show that the course needs revisiting.

Hoping that gives a clearer list of requirements...also please note that I am using Excell 2002

   
Reply to Nick M

3

 Mark, on Sep 30, 2009 2:28:03 pm BST

Nick

'Hello, I need a bit of help trying to format Excel

I need a cell to change colour in relation to the number of days expired to a given cell date:

for instance cell A1 has a date of 01/11/08 cell A1 should have a number of states depending on the number of days lapsed since that date.

So the states should be:

after 28 days cell turns Red
after 35 days cell turns Black'

Did yu get anywhere with ths? I am tyn something similar in Excel 2007 but need the date range 10 days and 20 days.

   
Reply to Mark
Ask your question Reply
They need your help