Conditional Format

[John]

Hi

I would like to colour a cell based on 4 different
criteria. eg: if cell A1=1 then red, A1=2 then green, A1=3
then blue, A1=4 then purple and of A1 is blank then no
formatting. Can anyone help?

Thanks


John

 

[VENKAT]

assume data is in A1 to A12
use this vba programme (may not be elgant)
for numbers for various colors see help <colorindex property>

----------------------
Public Sub test()
Dim cell As Range
For Each cell In Range("A1:A12")
cell.Activate

If ActiveCell = 1 Then
ActiveCell.Font.ColorIndex = 3
ElseIf ActiveCell = 2 Then
ActiveCell.Font.ColorIndex = 4
ElseIf ActiveCell = 3 Then
ActiveCell.Font.ColorIndex = 5
ElseIf ActiveCell = 4 Then
ActiveCell.Font.ColorIndex = 7
Else
End If
Next
End Sub

-------------

 

[xDeniumx]

5/7/04 16:03 Jon Smith
5/10/04 9:02 Raul
5/10/04 15:59 Joe Bloke
5/11/04 8:57 Ben Over
5/11/04 15:59 Smiley Face
5/12/04 9:06 Ronaldo
5/12/04 15:59 Shaft
5/13/04 9:27 Lorna
5/13/04 16:12 RVN
5/14/04 8:19 Roger


I want to do something similar with these data. As you can see, it'
date and time. What I want to do is to highlight all the cells wher
the time is over 9:00. Can someone help?

Ideally I would have preferred that the name of the people along wit
the time and date to be pasted on another sheet, but i'm not sure ho
to do that.


NOTE: Time and Date are in the same column, while names are in
different colum

 

[Max]

Perhaps one way ..

Assuming the sample data:

5/7/04 16:03 Jon Smith
5/10/04 9:02 Raul
5/10/04 15:59 Joe Bloke
5/11/04 8:57 Ben Over
5/11/04 15:59 Smiley Face
5/12/04 9:06 Ronaldo
5/12/04 15:59 Shaft
5/13/04 9:27 Lorna
5/13/04 16:12 RVN
5/14/04 8:19 Roger

is in A1:A10

Let's split the data first

Select col A
Click Data > Text to columns
("Delimited" will be selected)
Click Next

In step2 of the wiz.:
Check the box for "space" > click Next

In step3 of wiz.:
In the data preview pane: click to select the first col (the dates col)
Select "MDY" from the drop menu under "Col data format"

Click Finish

The original data in col A will be split
into 4 cols, A to D: Date, Time, Name1 & Name2

Now to apply the cond format:

Select A110
(i.e. the entire sample data range)

Click Format > Conditional Formatting

Under Condition 1, make the settings:
Formula Is | =$B1>TIMEVALUE("9:00 AM")
Click Format button > Patterns tab > Light blue? > OK
Click OK at the main dialog

The above should accomplish what you want:
Rows where the time in col B is > 9:00 am
will be coloured light blue

 

[Max]

Oops, sorry, missed this line in your post..

but the suggested steps are similar ..
viz. split the Time and date into 2 separate cols first
via Data > Text to Columns
(Precaution: to prevent the above
overwriting the adjacent names col,
do insert an extra col first in-between)

Then just select the entire range
and apply the cond formatting

 

[VENKAT]

introduce headings for each column
highlight the whole data includng headings
click <data(menu)-filter-autofilter>
click arrow right of <time> column
click <custom>
in custom auto filter window
choose<greater than> and flll in righ hand side 9:00(use colon) and clik
ok.
only those data (all 3 columns) where time is >9:00 will be visible copy
this visible data somewhere else for f;uther manipulation.

 

[xDeniumx]

Select A110
(i.e. the entire sample data range)

Click Format > Conditional Formatting

Under Condition 1, make the settings:
Formula Is | =$B1>TIMEVALUE("9:00 AM")
Click Format button > Patterns tab > Light blue? > OK
Click OK at the main dialog

^
I tired this formula several times, but nothing happens. I managed t
split the data like you said, but that formula isn't working for me

Anyway, thanx for your help. I really appreciate it. The stuf
suggested by Venkat worked as well......Thx

Just want to get that formula right now....

 

[Max]

Formula Is | =$B1>TIMEVALUE("9:00 AM")

The above formula presumes that the Time is in col *B*
In the example given, the Time data is in B1:B10 (after split)

Maybe re-check on the above ..

Just change the reference "$B1" in the formula
to suit the Time col (i.e. the 1st cell reference in the Time col)
as it appears in your *actual* sheet layout
(Note: the $ sign is important)

If you'd like to have a sample book,
just post a "readable" email add here

 

[xDeniumx]

If I remove 'AM' from the formula, it works......but then time lik
12:20PM are also highlighted....



My email: (e-mail address removed)


Than

 

[Max]

If I remove 'AM' from the formula, it works......

Think it works with or without the 'AM'
(Tried it)

but then time like 12:20PM are also highlighted....

But wasn't this your spec? re your original post:

.. What I want to do is to highlight all the cells where
the time is over 9:00

I've sent the sample book over ..

 

[xDeniumx]

Email well received.

Thanks for the help :

 

[Max]

Email well received.

Thanks for the help

Pleasure` xDeniumx !
Thanks for the feedback