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!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
View attachment 23389

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?
Can you post the exact CF formula you are using? Also can you test your data type by entering in an empty cell in the first row: =ISNUMBER(A1) and copy it down far enough to sample a large number of col A cells?
 

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.

jewls567

New Member
Joined
Jun 1, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Bump - hoping someone might have a way to solve this issue. Thanks in advance! :)
 

jewls567

New Member
Joined
Jun 1, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Can you post the exact CF formula you are using? Also can you test your data type by entering in an empty cell in the first row: =ISNUMBER(A1) and copy it down far enough to sample a large number of col A cells?

Here is the formula I am currently using:
=OR($A1=200180,$A1=200181,$A1=200182,$A1=200252,$A1=200940,$A1=200065)

Below is the data range I have this rule set for (my report is filtering data so many rows are hidden):
1601505482419.png


I created a new column & entered the =ISNUMBER(A1) formula all the way down. I got TRUE back for each row that has a value.
 

Attachments

  • 1601505448002.png
    1601505448002.png
    19.3 KB · Views: 3

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If your range starts at A8 or A9 (hard to tell from your image), then you must replace $A1 with $A8 or $A9 after you select the full range you want to apply the rule to.
 

jewls567

New Member
Joined
Jun 1, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

If your range starts at A8 or A9 (hard to tell from your image), then you must replace $A1 with $A8 or $A9 after you select the full range you want to apply the rule to.
I tried that and it didn't change anything unfortunately. Do you know what could be causing the cells not to show as highlighted?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I tried that and it didn't change anything unfortunately. Do you know what could be causing the cells not to show as highlighted?
I can only guess you didn't do something right. Maybe you need to go back over this thread and review the procedure for setting up the CF. Did you select the correct range before setting the CF? The CF formula is: =OR($A8=200180,$A8=200181,$A8=200182,$A8=200252,$A8=200940,$A8=200065) assuming A8 is the first cell selected, and notice no quote marks around the numbers.
 

jewls567

New Member
Joined
Jun 1, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
I can only guess you didn't do something right. Maybe you need to go back over this thread and review the procedure for setting up the CF. Did you select the correct range before setting the CF? The CF formula is: =OR($A8=200180,$A8=200181,$A8=200182,$A8=200252,$A8=200940,$A8=200065) assuming A8 is the first cell selected, and notice no quote marks around the numbers.
Thank you so much! Once I switched the starting cell in the formula to A8, all of them worked! :) :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,441
Members
410,684
Latest member
LakTik
Top