Conditional Formatting for Multiple Values (Formula Not Working)

jewls567

New Member
Joined
Jun 1, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
I have a long list of numerical values in a column and I am trying to set up conditional formatting to highlight cells that contain specific values in blue. The values I am trying to apply this formatting to are the following:
  • 200180, 200181, 200182, 200252, 200940, 200065
After doing some research online, below is the formula I have been attempting to use via Conditional Formatting (Use a Formula to Determine Which Cells to Format Selection rule type) to complete this task:
=OR($A$1="200180","200181","200182","200252","200940","200065")
1601495238399.png
Excel accepts the formula I enter but does not highlight the applicable cells that contain those values. Can anyone please help me to fix any errors you see to achieve the highlighting that I am looking for?

I appreciate your help in advance!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
What is the range you are trying to apply this to?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you want this CF to apply to a range in col A, first select the range, then use this formula:
=OR($A1=200180,$A1=200181,$A1=200182,$A1=200252,$A1=200940,$A1=200065)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case if the values are numbers use the formula JoeMo has provided, if they are text enclose the values in quotes.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

See post #3, and select the entire col A. In your initial post you indicated: " I have a long list of numerical values ". If the values are text then enclose each individual number in the post #3 formula in quote marks.
 

jewls567

New Member
Joined
Jun 1, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
In that case if the values are numbers use the formula JoeMo has provided, if they are text enclose the values in quotes.

If you want this CF to apply to a range in col A, first select the range, then use this formula:
=OR($A1=200180,$A1=200181,$A1=200182,$A1=200252,$A1=200940,$A1=200065)

I have been selecting the entire range of column A to ensure the conditional formatting rule applies to all cells with values.

I tried both methods and was not able to get the conditional formatting to work correctly unfortunately.
When I remove the quotes from the formula as these are numbers, only one of the 6 values shows up as highlighted in the column. See below:
1601497349576.png


When I changed the values in Column A to text the conditional formatting rule does not highlight any of the values at all.

The rule always works if it's just one value I'm trying to highlight in the column. However, when I try using the "OR" function with multiple values, that is where nothing seems to work for me.
Is there anything else I need to try that you might be able to suggest?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
You said you were trying to apply it to the whole column but your image in post#6 shows you are applying it to A154:A673 :unsure:
 

jewls567

New Member
Joined
Jun 1, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
You said you were trying to apply it to the whole column but your image in post#6 shows you are applying it to A154:A673 :unsure:

My apologies, I should have been more clear, the image is correct. I'm applying the rule to all cells that have a value in column A (rows 8 through 673) - many of the rows are hidden due to data that I have filtered out in this report.

1601497998766.png
 

Watch MrExcel Video

Forum statistics

Threads
1,112,784
Messages
5,542,500
Members
410,559
Latest member
jordansmith6532
Top