# Conditional Formatting question.

#### Dom1234

##### New Member
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,

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### 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

##### New Member
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...

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:
Code:
``=((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

##### Well-known Member
Try this:

 =SUM((--ISNUMBER(SEARCH(IF(\$G4:\$J4<>"",\$G4:\$J4),\$B\$54:\$B\$75))))

<tbody>
</tbody>

<tbody>
</tbody>

In Applies to: you can put:

=\$G\$4:\$J\$4

Or a longer range:

=\$G\$4:\$J\$100

#### Dom1234

##### New Member
Try something like this for your CF rule:
Code:
``=((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

You are welcome.

1,102,840
Messages
5,489,183
Members
407,675
Latest member
meaghutter

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...