Color Coding
 

I am not sure how I do this. I am emailed a daily report with call times on it for my employee's. I need to create a macro that will take each call time and code it a color depending on the call time. I would use conditional formatting, but I would have to set that up on a daily basis as I don't create these reports.

Ex. Anything between 0:00:00 - 0:06:09 (green) 0:06:10 -0:07:10 (yellow) and 0:07:11 and above (red)
A2: 0:04:39 (need A2 to be green)
B2: 0:08:27 (need B2 to be red)
C2: 0:06:54 (need C2 to be yellow)

Any help provided will be very greatful.

Color Coding
 

One way:

Public Sub ApplyCF()
Dim rOldSelect As Range
Dim rOldActivate As Range
Set rOldSelect = Selection
Set rOldActivate = ActiveCell
Range("A:C").Select
Range("A1").Activate
With Selection.FormatConditions
.Delete
.Add _
Type:=xlExpression, _
Formula1:="=IF(A1<"""",A1<TIME(6,10,0))"
.Item(1).Interior.ColorIndex = 10
.Add _
Type:=xlExpression, _
Formula1:="=IF(A1<"""",A1<TIME(7,11,0))"
.Item(2).Interior.ColorIndex = 6
.Add _
Type:=xlExpression, _
Formula1:="=if(A1<"""",A1<1)"
.Item(3).Interior.ColorIndex = 3
End With
rOldSelect.Select
rOldActivate.Activate
End Sub


In article ,
"Thomas L" <Thomas wrote:

I am not sure how I do this. I am emailed a daily report with call times on
it for my employee's. I need to create a macro that will take each call time
and code it a color depending on the call time. I would use conditional
formatting, but I would have to set that up on a daily basis as I don't
create these reports.

Ex. Anything between 0:00:00 - 0:06:09 (green) 0:06:10 -0:07:10 (yellow) and
0:07:11 and above (red)
A2: 0:04:39 (need A2 to be green)
B2: 0:08:27 (need B2 to be red)
C2: 0:06:54 (need C2 to be yellow)

Any help provided will be very greatful.

Color Coding
 

Thomas,

You can still use CF, just use VBA to set it up.

With this code, select all of your target cells, then run the macro

Sub RAGStatus()
Dim sStart As String

sStart = ActiveCell.Address(False, False)
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "<--(""06:10:00""))"
.FormatConditions(1).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "=--(""06:10:00"")," & _
sStart & "<--(""07:10:00""))"
.FormatConditions(2).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "=--(""07:10:00""))"
.FormatConditions(3).Interior.ColorIndex = 3
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Thomas L" <Thomas wrote in message
...
I am not sure how I do this. I am emailed a daily report with call times
on it for my employee's. I need to create a macro that will take each call
time and code it a color depending on the call time. I would use
conditional formatting, but I would have to set that up on a daily basis as
I don't create these reports.

Ex. Anything between 0:00:00 - 0:06:09 (green) 0:06:10 -0:07:10 (yellow)
and 0:07:11 and above (red)
A2: 0:04:39 (need A2 to be green)
B2: 0:08:27 (need B2 to be red)
C2: 0:06:54 (need C2 to be yellow)

Any help provided will be very greatful.

Color Coding
 

Ok, thanks. This helps a lot. Just a quick side question. How do I save this so that I can access it whenever I receive the file? Or do I have to copy the file over every time?

"Bob Phillips" wrote:

Thomas,

You can still use CF, just use VBA to set it up.

With this code, select all of your target cells, then run the macro

Sub RAGStatus()
Dim sStart As String

sStart = ActiveCell.Address(False, False)
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "<--(""06:10:00""))"
.FormatConditions(1).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "=--(""06:10:00"")," & _
sStart & "<--(""07:10:00""))"
.FormatConditions(2).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "=--(""07:10:00""))"
.FormatConditions(3).Interior.ColorIndex = 3
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Thomas L" <Thomas wrote in message
...
I am not sure how I do this. I am emailed a daily report with call times
on it for my employee's. I need to create a macro that will take each call
time and code it a color depending on the call time. I would use
conditional formatting, but I would have to set that up on a daily basis as
I don't create these reports.

Ex. Anything between 0:00:00 - 0:06:09 (green) 0:06:10 -0:07:10 (yellow)
and 0:07:11 and above (red)
A2: 0:04:39 (need A2 to be green)
B2: 0:08:27 (need B2 to be red)
C2: 0:06:54 (need C2 to be yellow)

Any help provided will be very greatful.

Color Coding
 

Thanks, this helps a lot. Just a quick side question. How do I save a macro? Sorry if I sound a little blonde. I am new to the whole macro world. Also, is there a way to link 2 macros? For example, the one below for the time, but I also adjusted the one below for a percentage. Is there a way to link them so that it will run the two at once, or do I have to run them seperately?

"Bob Phillips" wrote:

Thomas,

You can still use CF, just use VBA to set it up.

With this code, select all of your target cells, then run the macro

Sub RAGStatus()
Dim sStart As String

sStart = ActiveCell.Address(False, False)
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "<--(""06:10:00""))"
.FormatConditions(1).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "=--(""06:10:00"")," & _
sStart & "<--(""07:10:00""))"
.FormatConditions(2).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "=--(""07:10:00""))"
.FormatConditions(3).Interior.ColorIndex = 3
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Thomas L" <Thomas wrote in message
...
I am not sure how I do this. I am emailed a daily report with call times
on it for my employee's. I need to create a macro that will take each call
time and code it a color depending on the call time. I would use
conditional formatting, but I would have to set that up on a daily basis as
I don't create these reports.

Ex. Anything between 0:00:00 - 0:06:09 (green) 0:06:10 -0:07:10 (yellow)
and 0:07:11 and above (red)
A2: 0:04:39 (need A2 to be green)
B2: 0:08:27 (need B2 to be red)
C2: 0:06:54 (need C2 to be yellow)

Any help provided will be very greatful.

Color Coding
 

For info on Macros, see David McRitchie's "Getting Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm


You can either rewrite a macro to combine the two, or you can call both
from a third:

Public Sub Combo()
foo
bar
End Sub

Public Sub foo()
MsgBox "foo"
End Sub

Public Sub bar()
MsgBox "bar"
End Sub



In article ,
"Thomas L" <Thomas wrote:

Thanks, this helps a lot. Just a quick side question. How do I save a
macro? Sorry if I sound a little blonde. I am new to the whole macro world.
Also, is there a way to link 2 macros? For example, the one below for the
time, but I also adjusted the one below for a percentage. Is there a way to
link them so that it will run the two at once, or do I have to run them
seperately?

Color Coding
 

Sorry Thomas, caught me in my sleep time (UK).

I would pout the macro in my Personal.xls file, add a button to the
toolbars, and assign the macro to that. That way it will be available every
time you open a new target file.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Thomas L" <Thomas wrote in message
...
Thanks, this helps a lot. Just a quick side question. How do I save a
macro? Sorry if I sound a little blonde. I am new to the whole macro
world. Also, is there a way to link 2 macros? For example, the one below
for the time, but I also adjusted the one below for a percentage. Is there
a way to link them so that it will run the two at once, or do I have to run
them seperately?

"Bob Phillips" wrote:

Thomas,

You can still use CF, just use VBA to set it up.

With this code, select all of your target cells, then run the macro

Sub RAGStatus()
Dim sStart As String

sStart = ActiveCell.Address(False, False)
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "<--(""06:10:00""))"
.FormatConditions(1).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "=--(""06:10:00"")," & _
sStart & "<--(""07:10:00""))"
.FormatConditions(2).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & sStart & "<""""," & _
sStart & "=--(""07:10:00""))"
.FormatConditions(3).Interior.ColorIndex = 3
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Thomas L" <Thomas wrote in message
...
I am not sure how I do this. I am emailed a daily report with call
times
on it for my employee's. I need to create a macro that will take each
call
time and code it a color depending on the call time. I would use
conditional formatting, but I would have to set that up on a daily basis
as
I don't create these reports.

Ex. Anything between 0:00:00 - 0:06:09 (green) 0:06:10 -0:07:10
(yellow)
and 0:07:11 and above (red)
A2: 0:04:39 (need A2 to be green)
B2: 0:08:27 (need B2 to be red)
C2: 0:06:54 (need C2 to be yellow)

Any help provided will be very greatful.