Condition Formatting!

[crapit]

How do I make a row of cell (shading) that change to red color when the word
"trial" or "limit" is entered?

i.e cell 3f contain the word "trial". From the column of trial to the 1st
column, the entire row 3a to 3f will be red?

a b c d e f
1
2
3 Trial
4
5
6

 

[E Oveson]

Here's some VBA you could paste into the codebehind page for that sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

If UCase(Target.Value) = "TRIAL" Or UCase(Target.Value) = "LIMIT" Then
Range("A" & Target.Row & ":" & Target.Address(False,
False)).Interior.Color = RGB(255, 0, 0)
End If

End Sub

-Erik

 

[Frank Kabel]

Hi
no need for VBA. Try:
- select rows A1:F6
- goto 'Format - Conditional Format'
- enter the following formula:
=COUNTIF($A1:$F1,"trial")+COUNTIF($A1:$F1,"limit")
-chhose your format

 

[Barbara Wiseman]

If you don't want to use VBA I think this might work.
Highlight a row,
Format, Conditional formatting
in the first box use dropdown to choose formula is
in next box type in =$e1="trial"
click on the format button and choose the format you want, ie use the
patterns tab at the top to choose a background colour.
click on the add button and do it again but this time with limit in the
formula.
Then click on OK button.
Copy this row,
Select all other rows you want to have this format and edit, paste special,
paste formats.

Hope this is useful,
Barbara

 

[crapit]

If the existing cell contain other format, how to make the conditional
format not affect it?

 

[Frank Kabel]

Hi
the conditional format 'overwrites' the existing format if the
condition is met. what else would you expect?

 

[Niels Bøge Egstrand]

is it possibel to opperate with 9 different collors in conditional format ?

 

[Dave Peterson]

Nope. You get 3 formats plus the "normal" format.

If you need more options, you'll need some event macro.

 

[gatwickxx]

In the conditional format box, in cell A3, select "Formula Is" rathe
than "Cell Value Is" and write:

=OR($A3="trial",$A3="Limit")

This is a logical formula returning TRUE if the condition is met. Th
formula is relative with the row and absolute for column A so it can b
copied onto different rows and across columns and therefore highligh
the entire row as required

 

[crapit]

So u mean if the condition isnt met, the format should retain its setting>?

 

[crapit]

If the condition isnt met, would conditional format default the cell color?

 

[Dave Peterson]

If the conditions aren't met, the format that you have set for that cell
appear. (all the CF formatting won't be shown.)

Sometimes a little dry run on a test cell in a test worksheet helps.

Put 3 conditional formats in A1 all based on cell value (1,2,3)

Then watch what happens when you type in 1, then 2, then 3, then 4.

 

[crapit]

But if a cell that has already been formatted, i.e cell a1 has cell shading
dark blue and i paste the conditional format over, eventhough condition not
met, the cell shading color become default. Why?

 

[Dave Peterson]

I'm not sure I understand.

If you formatted a cell and then applied the CF conditions, the cell will only
change formats if one of the CF formats are met.

If you've copied a cell that was formatted nicely and also had conditional
formatting, then when you paste this cell, it inherits all the formatting--both
the "regular" formatting and the conditional formatting.

But if a cell that has already been formatted, i.e cell a1 has cell shading
dark blue and i paste the conditional format over, eventhough condition not
met, the cell shading color become default. Why?

 

[crapit]

"If you formatted a cell and then applied the CF conditions, the cell will
only
change formats if one of the CF formats are met."

Im using the above mentioned method. but the cell default to no cell shading
instead (no filling)

 

[Dave Peterson]

How did you format the cell?

Did you apply a color? Change the Font?

If you do this formatting manually, then when you don't have any CF conditions
met, you'll see this formatting.

"If you formatted a cell and then applied the CF conditions, the cell will
only
change formats if one of the CF formats are met."

Im using the above mentioned method. but the cell default to no cell shading
instead (no filling)

 

[crapit]

i.e on Cell A1
Right-click, format cell, select pattern and choose desire color!

But if I copied a cell that has the cond. format, and paste only the format
on cell A1, the cell shading will be default (no fill)

 

[Dave Peterson]

Say you put conditional formatting in C1--but don't change the "normal"
formatting.

Then format A1 to have a fill color (format|cells stuff).

Then copy C1 and paste special|formats over A1.

A1 will inherit all the formats from C1--both the conditional formatting and the
"normal" formatting.

If you format C1 (Format|cells stuff), then copy|paste special|formats, then A1
will get those same formats.

What did you want/expect to happen when you pasted formats?

i.e on Cell A1
Right-click, format cell, select pattern and choose desire color!

But if I copied a cell that has the cond. format, and paste only the format
on cell A1, the cell shading will be default (no fill)

 

[crapit]

Finaly i get what you mean! But as you say earlier, the format retain its
original setting when the formula isnt TRUE.

But the format cond. only provide colors and fonts change accordingly, right