Easy Question...Conditional formatting?

[Vince]

Hopefully this should be easy for all the experts...

Cells G4 thru R4 contain the text, "Act" or "For"

Cells G30 thru R30 are filled/shaded in red.
Cells G31 thru R30 are filled/shaded in red.
Cells G32 thru R32 are filled/shaded in red.
etc.
etc.
etc.

The cells which I have shader/filled in red....and with conditioning
formatting (or something else) I'd like to somehow referance cells G4 thru
R4 and if these cells contain "For" then leave the shaded as in. However if
these cells (G4 thru R4) contain the word "Act" I'd like to remove the
fill/shading to no fill or shading.

Is this possible???

Thanks...Vince

 

[T. Valko]

Let's see if we have this straight...

Right now the range G30:R32 is filled in red.

If G4:R4 contain the word Act you want no fill color
If G4:R4 contain the word For you want to keep the red fill color.

Ok, question:

Should *every* cell in G4:R4 contain the certain word or should *any* cell
in that range contain the certain word? Is it possible that the range
contains *both* words at the same time?

 

[AltaEgo]

The way I read it , you have two formats: one if the relevant row 4 contains
ACT; another if row 4 contains FOR

You have alternatives. You can either set conditional formatting to remove
(hide) sheet level formatting or to change sheet level formatting

To set conditional formatting to remove sheet leave formatting if a value in
row 4 = ACT:

1) set up your desired format in the worksheet
2) click a column G value where conditional formatting is required.
3) choose Format, conditional formatting.
If any current conditional format exists, remove it.
4( create Condition 1

a) select the 'Formula is' dropdown
b) enter the formula:

=G$4="ACT"

Leave 'No Format Set'

5) Close conditional formatting

6) Copy/Paste Special/ Formats to relevant rows/columns


The alternative is to format what you wish to see when you have FOR in row
4.

Remove sheet level formatting or set desired alternate formatting

Follow steps 1-4a above

4B) enter the formula:

=G$4="FOR"

Update your formatting requirements using the [Format...] button.

5) Close conditional formatting

6) Copy/Paste Special/ Formats to relevant rows/columns


Conditional formats are read in Excel in order (condition 1, condition 2
then condition 3). The format applies in accordance with the first match
encountered. So, if you need multiple conditions just enter them in order of
you processing priority priority. For example, you may wish to highlight
values less than 1000 irrespective of ACT appearing in G4. if this is the
case =G$4<1000 becomes condition 1 and =G$4="ACT" becomes condition 2.

Hoping this is not becoming confusing,

Steve