Color Coding

T

Thomas L

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.
 
J

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
 
B

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)

Thomas L said:
I am not sure how I do this. I am emailed a daily report with call times
Click to expand...
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.
 
T

Thomas L

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?
 
T

Thomas L

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?
 
B

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)

Thomas L said:
Thanks, this helps a lot. Just a quick side question. How do I save a
Click to expand...
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?
 
Top