Conditional formatting to highlight multiple values with single rule?

I have a large spreadsheet and use conditional formatting to highlight the cell background of particular values (which are numbers). I have approximately 20 values that I need highlighted.

At the moment, I have a separate conditional formatting rule set up for each value I am interested in. However, it would be preferable if I could have a single conditional formatting rule/formula to do this, as the values I am interested in change from time to time and it would be faster to update a single formula.

Is it possible to do this?

For example, in my spreadsheet, I want to highlight any cell that has values 3, 42, 65, 87 etc.

Thanks in advance!

If you list the values in a range somewhere, you could name that range and then use a COUNTIF function to check if the value is present.

Regards, TMS

Suggest you post a sample workbook so we have something to play with.

Regards, TMS

You could have a list of numbers to be triggers for formatting then select the range that you want to have the Conditional Formatting applied to and use this formula.

Formula:

=COUNTIFS($I$1:$I$10,A1)>0

This example uses values entered in column A being formatted if the value is in column I. If the value in column A is a value in column I then it will be formatted according to the format that you choose

Dynamic Named Range: nrHL:

Formula:

=Criteria!$A$2:INDEX(Criteria!$A:$A,COUNTA(Criteria!$A:$A))

Conditional Formatting Formula:

Formula:

=COUNTIF(nrHL,$A2)

Applied to cells:

Formula:

=$A$2:$C$23

Built on the Sample Workbook provided by newdoverman

Please see the updated example,


Regards, TMS

This is the same logic just as TMS done with a different formula for the Named Range. This just shows a second way to create the dynamic criteria range. I'm not saying better, just different.


Named Range: CFList......

Formula:

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!A:A))

Conditional Format formula:

Formula:

=COUNTIF(CFList,A2)

This is the same as TMS just to show that the named range, CFList on Sheet2 works.

The use of a dynamic list for the criteria allows you to add or subtract from the criteria listing as required. There are a lot of applications for dynamic ranges so it is worth while checking out how they work.

@newdoverman: thanks for the feedback and rep

I'm not saying better, just different.

I would hazard different, not better OFFSET is volatile, INDEX isn't, hence my preference for INDEX in creating Dynamic Named Range(s).

The DNR I set up referred to a second sheet, Criteria


Regards, TMS

You're welcome. I think that the additions you made were important.

I think that the addition of the second worksheet was also a good idea as there could be various lists needed for the workbook that would not be ideally suited to being on the main worksheet. This should be better being on another worksheet.

Usually, row 1 would be a header row and your data would start in row 2. So, typically, you would select row 2 down and your CF Formula MUST refer to the first row of the selection.


Regards, TMS



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

I'm saying that, if, say, you select cells A2:A200 before applying the CF Rules, your formula must refer to cell A2. If you select cells A3:A200 before applying the CF Rules, your formula must refer to cell A3. Select cells A4:A200 before applying the CF Rules, your formula must refer to cell A4.