Excel font colour based on lookup table

  Stubacka 10:18 03 Feb 2004

This is probably very simple (if you know how).

I have a series of about thirty rows of seven numbers that do not change once they are entered. I want these numbers to change to red when they are later entered into a separate table elsewhere. (It's a lottery type thing that I'm trying to do)

Vlookup, Hlookup, Conditional formatting?

I can't quite grasp the lookup thing by using the help menu.

Thanks again

  VoG II 12:45 03 Feb 2004

Is there a pattern to the way these are "entered elsewhere". If so then conditional formatting should work.

e.g. if you want to change the colour of A1 if the same number is entered in A31 then in the Formula Is box you would enter


If the situation is more complicated than that then you might have to use a LOOKUP formula in the Formula Is box.

  Stubacka 13:44 03 Feb 2004

No pattern whatsoever, totally random and the same numbers might appear more than once (based on lottery numbers over a period of weeks)

  VoG II 17:36 03 Feb 2004

Can you tell us the ranges that hold (a) your original numbers and (b) the separate table.

If I understand correctly, you want the number in the first table to change colour if that number appears anywhere in the second table. Can the same number appear more than once in the first table?

  VoG II 18:53 03 Feb 2004

OK I've done a quick mock-up. In my case the first range is in B2:I2 and the second is B6:I6 (i.e. one row only for each but the following will work with multiple rows).

In B2 set a conditional format using Formula Is

=MATCH(B2, $B6:$I6, 0)

Click in B2, click the Format Painter button ("paintbrush") then drag over the rest of range 1 to copy the conditional format.

  Stubacka 12:36 04 Feb 2004

Tried your last example but it didn't work for me.

Table 1 range is A1:G30

Table 2 range is J1:Z6

Yes - any number (from 1 - 49) can appear more than once in each table.

J - Z are Saturday Lottery days (so any six numbers from 1 - 49 under J - Z

A1 - G30 are the numbers selected by each member We have 30 members each selecting 7 numbers that are kept until all numbers from any one row have been drawn in the Lottery (6 numbers entered into Table 2 each week under J - Z)

Numbers in Table 1 to change to red to highlight numbers that have been drawn in Table 2 over the period of weeks.

I hope I have explained this well enough for you to understand what we are trying to achieve.

  VoG II 12:42 04 Feb 2004

In A1 set a conditional format using the following in the Formula Is box

=MATCH(A1, $J1:$Z6, 0)

Then apply your desired format.

Click in A1, click the Format Painter button ("paintbrush") then drag over A1:G30 to copy the conditional format.

  Stubacka 13:53 04 Feb 2004

This is so frustrating as I know your solution should work.
However, Table 1 Conditional formatting stops working when I enter the full range for Table 2 i.e. $J1:$Z6.
It only works for a single column or row entry in Table 2 e.g $J1:$J6 or even $J1:$Z1
So each row in Table 1 will only address a single column or row in Table 2 and not the whole Table 2.

Sorry to keep sending this back to you but I know it should work and it doesn't.

  VoG II 17:34 04 Feb 2004

This is my fault - I should have checked using multiple rows first. This formula (clumsy as it is) entered into the Formula Is box works:


If you would like a copy of my workbook, e-mail me by clicking my yellow envelope.

  Stubacka 15:02 06 Feb 2004

Thanks VoG.

Works like a dream and will save me (and my work colleagues) loads of time on this and other spreadsheets for all sorts of applications.

Thanks again.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Indie publisher Canongate’s top 10 book covers of 2017

New iMac Pro release date, UK price & specs rumours

Tablettes Amazon Fire : quel modèle choisir ?