Excel......conditional formatting or range 5

[WingandaPrayer]

Hi everyone.

What I am trying to do,without sucess, is have a list in one column and when one of the items appears from the list in another cell I would like to shade that cell.

e.g. using numbers

List of numbers 1,17,25

In cell A1 is 18.....cell doesn't change
In cell A2 is 17.....cell to infill to Blue

Any help appreciated.

 

[DaleWatson123321]

Hi WingandaPrayer,

I hope all is well - that you're "still in flight".

Here's a solution that I believe you'll be pleased with...

Steps:

1) Assign a range name to your list. (I have used "list_1&quot.

2) In an adjacent column (I've used "B&quot, enter the following formula in the first cell: =VLOOKUP(A1,list_1,1,FALSE)

3) If you have existing values in your "input" column ("A" in your example) that you want to preserve, copy them to another column. As the last step, you will copy them back to column A.

4) In cell A1, use Conditional Formatting...
a) From the menu: Format - Conditional Formatting,
b) Change "Cell Value Is" to "Formula Is",
c) Hit <Tab>, and enter the formula: =B1,
d) Click &quot;Format...&quot;, click the &quot;Patterns&quot; tab, and pick your color preference.

4) Copy A1 & B1 down for each row required.

5) If you preserved your numbers (see step #3), then:
a) Copy those values,
b) Go to cell A1, and use Edit - Paste Special - Values.

That's all. Except you might decide to move your VLOOKUP formula column &quot;off to the side&quot;, or possibly hide it.

I hope this is what you were seeking. Please advise as to how it &quot;fits&quot;.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[WingandaPrayer]

Thanks Dale.

I'll say a little.......and hope it works.....i'm sure it will.

Cheers

David

 

[DeLaMartre]

Brilliant, Dale!

This is particularly good advice, as it is so applicable to other areas.

I will try to remember to give you a star when I get to work tomorrow, (still having problems with being able to give stars from this computer, for some peculiar reason...).

I hope all is well in the Northland!

-Bob in California

 

[PeterMoran]

Hi All,

Brilliant again Dale! (I agree with Bob!)

I have given you a star.

It should be in the FAQs.

Regards,

Peter Moran

 

[WingandaPrayer]

Morning(well it is here) Dale,

Thanks for that.

If I could expand on my original question(yes I know i'm a pain in the ****)

If I wanted to inlcude more than one column to check.
e.g. from A1 To D4. Therefore the 'E' column is where I enter the VLOOKUP, which part of =VLOOKUP(A1,list_1,1,FALSE)
would I have to change or do I alter the conditional formatting or neither.I'm not near Excel to test at the moment.
I'm I causing confusion

example
A B C D
5 12 14 16
4 14 7 27
10 68 5 66
79 5 4 70

so if 5 is entered in list_1 then cells A1,C3,B4 would be highlighted.

Cheers

David

 

[MRussell]

Hello!
If I am understanding the most recent question correctly - here is my solution. In Excel 2000, conditional cell formatting can use a formula as a criteria, AND the formula must return a logical value of TRUE or FALSE. So - the trick (for me anyway) is to write a formula in that evaluates each cell in range A14 to each cell in list_1 and returns either TRUE or FALSE. Because the conditional formatting dialog box does not allow the user to use the paste functon formula palette to &quot;write&quot; a formula (heads up Microsoft - this would be someting to add to Excel 2003!), or allow the use of arrays, intersections or unions, I resort to literally writing a formula in a blank cell on my spreadsheet that will evaluate a cell for the values in the list and then copy and paste the formula into the conditional formatting dialog box, adjusting absolute cell references as needed.

Here is what the forumla in my Conditional Formatting Dialog box looks like for cell B3 when the list of values I am using (list_1) is in cells E3:E5:

=OR(EXACT(B3,$E$3),(EXACT(B3,$E$4)),(EXACT(B3,$E$5)))

Now the cell format can be copied to any cell in the spreadsheet and will always check the value in cells E3:E5 (list_1)to determine conditional formatting.

More information on using formulas to control formatting can be found in Excel Help. Just remember that Help instructions - without YOUR creative input - are merely text, WITH your input they can become genius.

Have fun!

M Russell

 

[DaleWatson123321]

Hi WingandaPrayer,

To adjust for your revised task, I would simply do this:

1) Move the formulas in Column B &quot;off to the side&quot; (for my example, I've used Column AA).

2) Copy the formulas from Column AA to AB,AC,AD.

3) Copy the &quot;Conditional Formatting&quot; from Column A to B,C,D.

Hope you find this appropriate.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

P.S. Thanks, guys, for the STARS. It's appreciated.

...Dale

 

[WingandaPrayer]

Thanks Dale & MRussell.

Back using Excel and both work as I require

Using Exact I was lazy =OR(EXACT(A1,$I$1:$I$20))

Thanks again.


Stars to both

 

[DaleWatson123321]

DeLaMartre,

Hi Bob,

...a BIG &quot;THANKS&quot; !!! ...I did notice the STAR you gave.

I'm also pleased you were able to visualize additional uses from this Conditional Formatting example.

Best Regards, ...Dale