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,
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top