Conditional Format - if a cell's exact value is found in a range of cells then apply condition

Mandy_

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
Hi,

I would like some help please. I’m not sure what formula I should be using.

I’ve created a priority matrix. Using Microsoft 365. Image below.

I have created the tasks list, and when I enter data in the list the tasks then appear in the correct quadrants depending on the yes/no answers for urgent/important.

As I have been unable to work out how to delete the task from the quadrant itself – for if I do delete the task from the quadrant, then any future tasks won’t get copied across from the list. So I am wanting to create a conditional format rule to highlight the completed tasks in the list so I can easily see which lines to clear.

I have set up the helper columns to reflect the quadrants, and am using the formula in the bc and bd columns =IF(LEN($B8)>0,(INDEX($K$8:$K$67,MATCH($C8,range3,0),1)))

So – if any value is recorded next to the task in the quadrant (i.e. column B and column F,) then the helper column will display the task name, otherwise false.


My question from here then is – if the cells in the helper columns display text, find the text in column K and highlight.


I've spent nearly a week on this! and I can get the result if I use the formula

=$K8=$BC$8 where the conditional rule applies to $K$8:$O$67

=$K8=$BC$9

=$K8=$BC$10

etc and in the screen shot, I've only applied the rule =$K8=$BC$8, I haven't applied for the other cells, so task 2 should also be highlighted if my formula was correct. Which it isn't!


I was hoping with 60 lines (30 lines per column) – I was trying to have an encompassing formula rather than setting a conditional rule 60 times!

I’ve also tried =$K8=$BC$8:$BD$39

And I’ve tried sumproduct, sumif, countif – but I am new to excel and unsure of the syntax’s and I’m not quite understanding how to get the result I am after.

If anyone had any ideas I’d really appreciate it.



Thank you for your help.



Screen Shot 2021-01-29 at 4.03.00 pm.png
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

aRandomHelper

Board Regular
Joined
Jan 14, 2021
Messages
225
Office Version
  1. 2016
Platform
  1. Windows
Something like this maybe?
Book1.xlsm
KLMNOBCBD
7Header1Header2Header3Header4Header5
8Task1Task1Task2
9Task2Task5Task9
10Task3
11Task4
12Task5
13Task6
14Task7
15Task8
16Task9
17Task10
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K8:O17Expression=COUNTIF($BC:$BD,$K8)>0textNO
 
Solution

Mandy_

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
Thank you! This solved it.

May I ask please - so I can understand, in your formula the columns $BC:$BD are without a row number, why is this?
 

aRandomHelper

Board Regular
Joined
Jan 14, 2021
Messages
225
Office Version
  1. 2016
Platform
  1. Windows
columns $BC:$BD are without a row number, why is this?
That means it's referring to the entire columns BC and BD. You can also restrict the range to $BC$8:$BD$39, for example, if there are parts (above or below) you don't want the formula to search for a match.
 

Mandy_

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
That means it's referring to the entire columns BC and BD. You can also restrict the range to $BC$8:$BD$39, for example, if there are parts (above or below) you don't want the formula to search for a match.
Thank you for explaining. I really appreciate your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

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
Top