Condition Formatting!

  • Thread starter Thread starter crapit
  • Start date Start date
C

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
 
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
 
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
 
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
 
If the existing cell contain other format, how to make the conditional
format not affect it?
 
Hi
the conditional format 'overwrites' the existing format if the
condition is met. what else would you expect?
 
is it possibel to opperate with 9 different collors in conditional format ?
 
Nope. You get 3 formats plus the "normal" format.

If you need more options, you'll need some event macro.
 
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
 
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.
 
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?
 
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?
Click to expand...
 
"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)
 
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)
Click to expand...
 
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)
 
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)
Click to expand...
 
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
 
Back
Top