Conditional Format

  • Thread starter Thread starter John
  • Start date Start date
J

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

-------------
 
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
 
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
Click to expand...

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 A1:D10
(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
 
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
 
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.
 
Select A1:D10
(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....
 
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
 
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
 
xDeniumx > said:
If I remove 'AM' from the formula, it works......
Click to expand...

Think it works with or without the 'AM'
(Tried it)
but then time like 12:20PM are also highlighted....
Click to expand...

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
Click to expand...

I've sent the sample book over ..
 
Back
Top