Color Coding

[Guest]

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.

 

[JE McGimpsey]

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

 

[Bob Phillips]

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)

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.

 

[Guest]

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?

 

[Guest]

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?

 

[JE McGimpsey]

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

 

[Bob Phillips]

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)

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?