Conditional Formatting question.

Dom1234

New Member
Joined
Aug 21, 2019
Messages
3
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,
 

Some videos you may like

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,745
Office Version
365
Platform
Windows
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
Joined
Aug 21, 2019
Messages
3
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,745
Office Version
365
Platform
Windows
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
Joined
Dec 3, 2018
Messages
12,224
Office Version
2007
Platform
Windows
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
Joined
Aug 21, 2019
Messages
3
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,745
Office Version
365
Platform
Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top