cell colour

R

rodw

i need to change a cells color basses on a forrmula

basically say i have A1 and B1


I want a1 to go red if B1 = "test1" and A1>4:00

or if B1="test2" and A1>8:00

so it will only go red if b1 = test1 and A1 is greter than 4:00

but if b1="test2" it will only go red when A1 is greter than 8:0
 
J

JulieD

Hi

assuming 4:00 & 8:00 are times then

click in cell A1, choose format / conditional formating and choose
Formula Is
and then type
=AND(B1="Test1",A1>0.166667)
click on the format button & set the format then click ADD
choose
Formula Is
then type
=AND(B1="Test2",A1>0.33333)
and set the format

Click OK

Cheers
JulieD
 
R

rodw

i was going to use this but you can only have 3 conditional formats,

is there any other way
 
R

rodw

I am getting confused by this,


i tried the worksheet change but as the time is entered as a formul
will not work.


I can see that you need worksheet calculate but cant see how to code


i basically have rows of times "in" , "out" and "type" i need t
highlight the in and out times in red if it exceeds 4:00 when type i
"one"
and highlight in and out in red when it exceeds 8:00 and the type i
"two
 
D

David McRitchie

Hi "rod >",
You have only one color to be conditionally added and it is red,
so you have only one format condition as far as counting C.F.
conditions per cell. See Julia's reply about
using =AND(condition1, condition2, ...)
except that you also have an OR
=OR(AND(cond1,cond2), AND(cond3, cond4) )
see below for more complete reply.

Counting Conditions is per cell, each cell has it's own set of
conditions and is independent of the C.F. of other cells. Though
usually all cells have the same formulas (relative to active cell
when entered).

Conditional Formatting is preferable to Event Macros,
and it does not make any difference how the value changes.
With Event macros you have to choose the event to match the
situation and for formulas you have to trigger on calculation not
on changed value constant.

Looking back at your original posting, it is very convoluted, misspelled words
do not help. If you are using English language software for posting,
please use spell checking. You will need to use both OR and AND
I can't completely follow but something like. But I will try to use both
the original and later together.

To me and especially your original question and even your
restatement do not appear to be clearly written. When
dealing with logic you must use AND and OR correctly
they are not interchangeable and when you use the word "it" it must
be clear what you are referring to. even if it takes extra words
to make things easier for the reader.

If you want to do this for the entire column A
With Column A selected and A1 as the active cell
=OR(AND($B1="one",$A1>TIME(4,0,0)),AND($B1="two",$A1>TIME(8,0,0)))

Note 4 hours is 4/24 of a day or as decimal .1666666666
but the use of TIME(hrs,mins,secs) is less ambiguous and
more accurate than trying to use a decimal constant.

Use of the $ signs means that the specific columns are tested no matter
which cell the conditional formatting is in -- so it would be possible to
color the entire row if you wanted to. My page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

If you are going to calculate times differences, which is something that
spreadsheets can also be used for, I have a page on date and time
http://www.mvps.org/dmcritchie/excel/datetime.htm
 
Top