Conditional Formatting question.

[Dom1234]

Hi,

formula question for Excel 2010

I would like to have 4 cells change colour, if text taken from a range of cells appears within those 4 cells.

cells I want to change colour G4 to J4

cells that contain different text options B54 to B75

Example.

If I write the text from Cell B60 into cell H4 then cells G4 to J4 turn green.

thanks,

 

[Joe4]

Welcome to the Board!

If I write the text from Cell B60 into cell H4 then cells G4 to J4 turn green.

So, are you saying that if the contents of H4 match the contents of B60, then G4:J4 should turn green?
Then, set up CF in the following way:
1. Select the cells G4:J4
2. Go to Conditional Formatting and choose the Formula option (last one)
3. Enter the following formula:
=$H$4=$B$60
4. Choose your green formatting option
5. Click OK

 

[Dom1234]

Welcome to the Board!


So, are you saying that if the contents of H4 match the contents of B60, then G4:J4 should turn green?
Then, set up CF in the following way:
1. Select the cells G4:J4
2. Go to Conditional Formatting and choose the Formula option (last one)
3. Enter the following formula:
=$H$4=$B$60
4. Choose your green formatting option
5. Click OK

That scenario does work however i need to formula to work for multiple cases. so...

if G4 = contents from any cell between B54:B75 turn G4:J4 green
if H4 = contents from any cell between B54:B75 turn G4:J4 green
if I4 = etc....
and if J4 = etc...

to add more context.

G4:J4 represent 4 quarters to a month.
B54:75 represents initals for employees..
a check needs to be completed per month...

so the employee would initial in which quarter they have done the check...if the check is done then g4:j4 can be greened out to indicate the check has been completed for that month.

hope that makes more sense.

 

[Joe4]

Try something like this for your CF rule:

=((COUNTIF($B$54:$B$75,$G$4) + COUNTIF($B$54:$B$75,$H$4) + COUNTIF($B$54:$B$75,$I$4) + COUNTIF($B$54:$B$75,$J$4)) > 0

 

[DanteAmor]

Try this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 800"]
<tbody>[TR]
[TD]=SUM((--ISNUMBER(SEARCH(IF($G4:$J4<>"",$G4:$J4),$B$54:$B$75))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

In Applies to: you can put:

=$G$4:$J$4

Or a longer range:

=$G$4:$J$100

 

[Dom1234]

Try something like this for your CF rule:

=((COUNTIF($B$54:$B$75,$G$4) + COUNTIF($B$54:$B$75,$H$4) + COUNTIF($B$54:$B$75,$I$4) + COUNTIF($B$54:$B$75,$J$4)) > 0

worked perfectly...thanks a lot

 

[Joe4]

You are welcome.