Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / September 2004

Tip: Looking for answers? Try searching our database.

Conditional Format

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
John - 16 Sep 2004 02:28 GMT
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 - 16 Sep 2004 06:38 GMT
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

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

> Hi
>
[quoted text clipped - 6 lines]
>
> John

Signature

Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

xDeniumx - 16 Sep 2004 10:09 GMT
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

--
Message posted from http://www.ExcelForum.com
Max - 16 Sep 2004 12:28 GMT
Perhaps one way ..

Assuming the sample data:

> 5/7/04 16:03 Jon Smith
> 5/10/04 9:02 Raul
[quoted text clipped - 6 lines]
> 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 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
Signature

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

> 5/7/04 16:03 Jon Smith
> 5/10/04 9:02 Raul
[quoted text clipped - 20 lines]
> ---
> Message posted from http://www.ExcelForum.com/
Max - 16 Sep 2004 12:38 GMT
Oops, sorry, missed this line in your post..

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

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

Signature

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

VENKAT - 17 Sep 2004 06:09 GMT
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.
-------------------------------------

> 5/7/04 16:03    Jon Smith
> 5/10/04 9:02    Raul
[quoted text clipped - 20 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Signature

Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

xDeniumx - 17 Sep 2004 07:14 GMT
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....

--
Message posted from http://www.ExcelForum.com
Max - 17 Sep 2004 07:51 GMT
> 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
Signature

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

> Select A1:D10
> (i.e. the entire sample data range)
[quoted text clipped - 17 lines]
> ---
> Message posted from http://www.ExcelForum.com/
xDeniumx - 17 Sep 2004 12:05 GMT
If I remove 'AM' from the formula, it works......but then time lik
12:20PM are also highlighted....

My email:  Denium3008@yahoo.com

Than

--
Message posted from http://www.ExcelForum.com
Max - 17 Sep 2004 14:31 GMT
> 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 ..
Signature

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

xDeniumx - 18 Sep 2004 06:37 GMT
Email well received.

Thanks for the help   :
Max - 18 Sep 2004 11:31 GMT
> Email well received.
>
> Thanks for the help   :)

Pleasure` xDeniumx !
Thanks for the feedback
Signature

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.