Hi All,

Im working on a large workbook with multiple worksheets. Each of these
worksheets has a large amount of data relating to training records. In
particular, the date that an employees certification is due to expire
(eg: 01/02/2007 or 31/09/2009 etc).

I need to color code these records with the below logic, eg:

If Cell Date is <Today() = Red Text or Cell
If Cell Date is <18months from Today()+1 = Yellow Text or Cell Cell
If Cell Date is >18months from Today() = No change - leave white

I have limited VBA knowledge, but will be able to work my way through
some of the code that is posted.

It's also key to note that there are multiple 'Blank' and 'Text' Cells
in these worksheets. I only want to apply this code to a cell if it is
populated with a date, is this possible ?? (eg: i only want to change
the color of the dates listed, not the other cells with text and single
numbers).

An extract of the spreadsheet can be provided if this will assist.

Thanks in advance for all of your help with this post.

Cheers,

Darren

(PS - My Newsgroup email is not legitimate due to potential SPAM emails
- please post a reply initially - Thanks)



*** Sent via Developersdex http://www.developersdex.com ***

Hi Darren,

check Conditional Formating in Format menu

"Darren Hastie" wrote:

> Hi All,
>
> Im working on a large workbook with multiple worksheets. Each of these
> worksheets has a large amount of data relating to training records. In
> particular, the date that an employees certification is due to expire
> (eg: 01/02/2007 or 31/09/2009 etc).
>
> I need to color code these records with the below logic, eg:
>
> If Cell Date is <Today() = Red Text or Cell
> If Cell Date is <18months from Today()+1 = Yellow Text or Cell Cell
> If Cell Date is >18months from Today() = No change - leave white
>
> I have limited VBA knowledge, but will be able to work my way through
> some of the code that is posted.
>
> It's also key to note that there are multiple 'Blank' and 'Text' Cells
> in these worksheets. I only want to apply this code to a cell if it is
> populated with a date, is this possible ?? (eg: i only want to change
> the color of the dates listed, not the other cells with text and single
> numbers).
>
> An extract of the spreadsheet can be provided if this will assist.
>
> Thanks in advance for all of your help with this post.
>
> Cheers,
>
> Darren
>
> (PS - My Newsgroup email is not legitimate due to potential SPAM emails
> - please post a reply initially - Thanks)
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Hassan,

Sorry i forgot to mention, i've tried the Conditional Formatting and:

1: I will need more than 3 conditions in the future.

2: I couldnt get it to apply the conditions properly.

I was hoping there would be some VBA code that would do this for me.

Cheers,

Darren


*** Sent via Developersdex http://www.developersdex.com ***

Hi Darren,

Your request doesn't sound too difficult. However, a few questions first.

Can one assume that the dates are restricted to specific columns on the
worksheets?

Are they in the same columns in all of the worksheets? If so which columns?

Do you require all of the worksheets to be processed or just specific ones.

Do you want the macro to run each time you open the worksheet? (It can be
controlled to run only the first time you open it each day).

Assuming that Today()+1 = 3 Aug 2007, how do you want to handle
Today()+1 + 18 months. Is it sufficient to calculate it as:-
=Today()+1 + (365 x 1.5) which returns 31 Jan 2009 or
do you want it to return 3 Feb 2009?

Cells can be tested for a date value.

What version of xl do you have?

When do you need it completed by? Might take a couple of days depending on
whether I need more info from you and how quickly you reply when I do.

Regards,

OssieMac



"Darren Hastie" wrote:

> Hi All,
>
> Im working on a large workbook with multiple worksheets. Each of these
> worksheets has a large amount of data relating to training records. In
> particular, the date that an employees certification is due to expire
> (eg: 01/02/2007 or 31/09/2009 etc).
>
> I need to color code these records with the below logic, eg:
>
> If Cell Date is <Today() = Red Text or Cell
> If Cell Date is <18months from Today()+1 = Yellow Text or Cell Cell
> If Cell Date is >18months from Today() = No change - leave white
>
> I have limited VBA knowledge, but will be able to work my way through
> some of the code that is posted.
>
> It's also key to note that there are multiple 'Blank' and 'Text' Cells
> in these worksheets. I only want to apply this code to a cell if it is
> populated with a date, is this possible ?? (eg: i only want to change
> the color of the dates listed, not the other cells with text and single
> numbers).
>
> An extract of the spreadsheet can be provided if this will assist.
>
> Thanks in advance for all of your help with this post.
>
> Cheers,
>
> Darren
>
> (PS - My Newsgroup email is not legitimate due to potential SPAM emails
> - please post a reply initially - Thanks)
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

i don't see the logic here, seems to be missing some criteria, but maybe this
will give you an idea:

Sub Color_Cells()
Dim ws As Worksheet
Dim cell As Range
For Each ws In ThisWorkbook.Worksheets
For Each cell In ws.UsedRange
If IsDate(cell) Then
If cell < Now() And cell > DateSerial(Year(Now()), _
Month(Now()) - 18, Day(Now())) Then
cell.Font.ColorIndex = 6
ElseIf cell < DateSerial(Year(Now()), Month(Now()) - 18, _
Day(Now()) + 1) Then
cell.Font.ColorIndex = 3
End If
End If
Next
Next
End Sub

--


Gary


"Darren Hastie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi All,
>
> Im working on a large workbook with multiple worksheets. Each of these
> worksheets has a large amount of data relating to training records. In
> particular, the date that an employees certification is due to expire
> (eg: 01/02/2007 or 31/09/2009 etc).
>
> I need to color code these records with the below logic, eg:
>
> If Cell Date is <Today() = Red Text or Cell
> If Cell Date is <18months from Today()+1 = Yellow Text or Cell Cell
> If Cell Date is >18months from Today() = No change - leave white
>
> I have limited VBA knowledge, but will be able to work my way through
> some of the code that is posted.
>
> It's also key to note that there are multiple 'Blank' and 'Text' Cells
> in these worksheets. I only want to apply this code to a cell if it is
> populated with a date, is this possible ?? (eg: i only want to change
> the color of the dates listed, not the other cells with text and single
> numbers).
>
> An extract of the spreadsheet can be provided if this will assist.
>
> Thanks in advance for all of your help with this post.
>
> Cheers,
>
> Darren
>
> (PS - My Newsgroup email is not legitimate due to potential SPAM emails
> - please post a reply initially - Thanks)
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

OssieMac...

The dates are not always in the same cells (it varies and there can be
up to 30 columns and 500 rows to check per sheet). The macro will also
need to be applied to multiple worksheets. It may be easier said than
done but basically i want the macro to search for a cell with a date in
it, once located, check to see what formatting needs to be applied (eg:
Change to Red, Green, Yellow, etc)...

Im happy to run the macro manually for now, but eventually it will be
something that i need to apply monthly (but for now, manual is fine)...

As for the dates (31 Jan 2009 or 03 Feb 2009) it needs to be based on
Days not months/years (1.5 years equalling 548 days)...basically i need
a warning to appear (color coding) if the certification im looking at
has past an 18month (548 day) period...

Im utilising MS Excel 2003

I dont have a specific deadline for this, i just need to analyse the
data for trends at this stage then apply it to some monthly reports...

Cheers...Darren

*** Sent via Developersdex http://www.developersdex.com ***

Hi Again Darren,

Gary Keramidas has given you some code which I assume works and I extend my
thanks to him for his contribution.

However, I have modified it a bit to include your 548 days and used Case
because I think it is easier to understand and modify if you change your
mind. However, you need to realise with case that it processes only the first
true case. If there is more than one true then the extras are ignored. Of
course if none are true then Else runs.

The other thing is that you need to have the case statements in the correct
order if there is a priority. For example if you put the line 'Case Is < Date
+ 1 + 548' first then it would also run when the date is actually less than
today() because any date less than Date + 1 + 548 is true.

I have also put the code in to color the cell instead of the font. I find
that it is easier to find the cell and also yellow font is almost impossible
to read. If you want the font colored then simply remove the single quote
which makes it a comment and place it in front of the interior color lines of
code. There is a nice little table in help which you will find under 'color
index' if you want other colors.

Lastly, the first case is <= to include today(). If you do not want to
include today in the red then remove the = and it will then be included in
the yellow. Also the yellow does not include the last day in the second case;
only less than. Of course the +1+548 could be 549. I only put it that way
because that is how you described it.

Sub Color_Cells()
Dim ws As Worksheet
Dim cell As Range

For Each ws In ThisWorkbook.Worksheets

For Each cell In ws.UsedRange

If IsDate(cell) Then

Select Case cell

Case Is <= Date
'cell.Font.ColorIndex = 3 'Red
cell.Interior.ColorIndex = 3

Case Is < Date + 1 + 548
'cell.Font.ColorIndex = 6 'Yellow
cell.Interior.ColorIndex = 6

'Else is needed in case you correct a date
Case Else
'cell.Font.ColorIndex = 1 'Black
cell.Interior.ColorIndex = xlColorIndexNone

End Select

End If

Next cell

Next ws

End Sub

Regards,

OssieMac



"Darren_New2VBA" wrote:

> OssieMac...
>
> The dates are not always in the same cells (it varies and there can be
> up to 30 columns and 500 rows to check per sheet). The macro will also
> need to be applied to multiple worksheets. It may be easier said than
> done but basically i want the macro to search for a cell with a date in
> it, once located, check to see what formatting needs to be applied (eg:
> Change to Red, Green, Yellow, etc)...
>
> Im happy to run the macro manually for now, but eventually it will be
> something that i need to apply monthly (but for now, manual is fine)...
>
> As for the dates (31 Jan 2009 or 03 Feb 2009) it needs to be based on
> Days not months/years (1.5 years equalling 548 days)...basically i need
> a warning to appear (color coding) if the certification im looking at
> has past an 18month (548 day) period...
>
> Im utilising MS Excel 2003
>
> I dont have a specific deadline for this, i just need to analyse the
> data for trends at this stage then apply it to some monthly reports...
>
> Cheers...Darren
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Here is a simple example that you can adapt


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Darren_New2VBA" <(E-Mail Removed)> wrote in message
news:ebll$(E-Mail Removed)...
> Hassan,
>
> Sorry i forgot to mention, i've tried the Conditional Formatting and:
>
> 1: I will need more than 3 conditions in the future.
>
> 2: I couldnt get it to apply the conditions properly.
>
> I was hoping there would be some VBA code that would do this for me.
>
> Cheers,
>
> Darren
>
>
> *** Sent via Developersdex http://www.developersdex.com ***