Conditional Formatting for Multiple Values (Formula Not Working)

[jewls567]

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")​

​

​

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]

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?

 

[jewls567]

Bump - hoping someone might have a way to solve this issue. Thanks in advance!

 

[jewls567]

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):

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.

 

[JoeMo]

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]

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]

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]

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!

 

[JoeMo]

Thank you so much! Once I switched the starting cell in the formula to A8, all of them worked!

Guess you missed post #14 ......