Conditional Formatting for multiple cell values with a single formula

SlamDunk

New Member
Joined
Jun 10, 2015
Messages
8
Hi everyone,
I've searched in the forum for hours and I still can't find a solution to a relatively simple problem of mine.

I want to do a CaSE SeNSiTiVE conditional format if a cell value either equals "EXAMPLE" or "example" [and nothing else]
I want to do a separate conditional format if a cell value either equals "test" or "Test" or "TEST" [and nothing else]
You get my drift..

I could only manage to create conditional formatting rules for one value at a time, using formulas like

=EXACT(A1:AM1,"EXAMPLE")
or
=FIND("EXAMPLE",A1:AM1,1)

Both of the above formulas work only for one value. I have too many values to create a formula for each value, so I want to somehow group the values inside a single formula.

I have tried things like

=OR(EXACT(A1:AK1,"EXAMPLE"),EXACT(A1:AK1,"example"))
etc. etc.

So if I can somehow group the values, then I will only need to have one formula for each desired conditional formatting.

Any help from you lot will be much much appreciated.

Thanks

This is my first post, please ask if anything is not clear. Thank you :)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
There's a shorter method but it's not accepted by Conditional Formatting. So you would need to enter this into a hidden cell, and then test the hidden cell for TRUE/FALSE:
=OR(A1={"example","EXAMPLE"})
 
Upvote 0
There's a shorter method but it's not accepted by Conditional Formatting. So you would need to enter this into a hidden cell, and then test the hidden cell for TRUE/FALSE:
=OR(A1={"example","EXAMPLE"})

Thanks for your reply Neil, I have loads of data so I'll try out your suggestion across my sheet and get back with an update in a couple of hours. The good news is that I can see this method working in my head at least :) Thanks again

Edit: I've reached my first hurdle already. This solution is not case sensitive :(
Brain scratching time!

Edit2: Combining your idea of using hidden cells and your formula, but with the use of the EXACT function lets me do exactly what I want. Yay!

Solution >>>>>>

=OR(EXACT(B2,"EXAMPLE"),EXACT(B2,"example"))
and then conditional format on the values of the hidden cells

Thank you! I'm sorted!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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
Back
Top