# conditional formatting

#### coltheplumb

##### Well-known Member
Hi All ok i have this in C/F in cell K4 =trim(\$f4)=”a” which turns cell K4 yellow when “a” is in f4 which works fine, how could i alter this so that it coves a bigger range, something like
=trim(\$f4, \$H4)=”a,b,c” so will turn Cell K4 yellow only if an a,b or c appears in both cells F4 and H4. Or is this not possible.
All help appreciated
Colin X

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try:
=AND(ISNUMBER(MATCH(TRIM(\$F4),{"a","b","c"},0)),ISNUMBER(MATCH(TRIM(\$H4),{"a","b","c"},0)))

It looked wonderful but when i put it in I got this you may not use, intersections, or array constants for C/F criteria

An alternative formula

=AND(\$F4<>"",ISNUMBER(SEARCH(TRIM(\$F4),"abc")),\$H4<>"",ISNUMBER(SEARCH(TRIM(\$H4),"abc")))

M.

No it doesn't like that either ;-(

No it doesn't like that either ;-(

Well it worked for me...

But, re-thinking, i realized that the formula has some flaws.

The best you can do is:
1. Create a range with 3 cells containing
a
b
c

2. Name it, say, MyRange

3. Use the formula suggested above by gsistek replacing the array {"a";"b";"c"} by MyRange, i.e.,

=AND(ISNUMBER(MATCH(TRIM(\$F4),MyRange,0)),ISNUMBER(MATCH(TRIM(\$H4),MyRange,0)))

Hope this helps

M.

No still not working for me, maybe cos im using 2010?

Replies
7
Views
421
Replies
5
Views
789
Replies
3
Views
546
Replies
5
Views
346
Replies
2
Views
1K

1,221,186
Messages
6,158,414
Members
451,492
Latest member
ichinisan123

### 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.

### Which adblocker are you using?

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

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