Conditional formatting & Vlookup

Hi there

Has any one a solution for validation lotto numbers.

In row 2 for example I have up to six numbers between 1 and 50 in colums 2 
to 7

In rows 5 to 10 I have random numbers between 1 and 50 in colums 2 to 7.
For each cell I want to mark with a green background a match with the 
numbers in row 2.

Thank you very much

Jean-Paul Hahn 


0
1/23/2006 9:40:02 PM
excel 39879 articles. 2 followers. Follow

5 Replies
690 Views

Similar Articles

[PageSpeed] 40

Use the COUNTIF() Function in Conditional formatting.

If the 6 numbers are in Range B2:F2 and you have randon numbers in
Range B10:G15, then select the latter range, ener Conditional
Formatting fro mthe Format menu. Choose "Formula Is" and enter :

"=IF(COUNTIF($B$2:$G$2,G15)>=1,TRUE,FALSE)" (no quotes)

then select the format you want to apply when the number is found.

0
1/23/2006 10:08:23 PM
Select B2:G2 and in CF, use a formula of =COUNTIF($B$5:$G$10,B2)>0 and then
select your colour.
--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Jean-Paul Hahn" <jean-paul.hahn@gmx.net> wrote in message
news:dr3ifi$vb6$1@news.hispeed.ch...
> Hi there
>
> Has any one a solution for validation lotto numbers.
>
> In row 2 for example I have up to six numbers between 1 and 50 in colums 2
> to 7
>
> In rows 5 to 10 I have random numbers between 1 and 50 in colums 2 to 7.
> For each cell I want to mark with a green background a match with the
> numbers in row 2.
>
> Thank you very much
>
> Jean-Paul Hahn
>
>


0
bob.phillips1 (6510)
1/23/2006 10:15:07 PM
Thank you very much for your tip. I had to adjust the formula to

=IF(COUNTIF($B$2:$F$2;B10)>0;TRUE;FALSE) for cell b10
=IF(COUNTIF($B$2:$F$2;C10)>0;TRUE;FALSE) for cell c10
=IF(COUNTIF($B$2:$F$2;D10)>0;TRUE;FALSE) for cell d10
=IF(COUNTIF($B$2:$F$2;E10)>0;TRUE;FALSE) for cell e10
=IF(COUNTIF($B$2:$F$2;F10)>0;TRUE;FALSE) for cell f10

and then copy/paste special, format to the other cells below.

Now everything works fine and I can quickly check wheter my numbers won in 
the lottery.


<mrjsoftware@hotmail.com> schrieb im Newsbeitrag 
news:1138054102.996188.208610@o13g2000cwo.googlegroups.com...
> Use the COUNTIF() Function in Conditional formatting.
>
> If the 6 numbers are in Range B2:F2 and you have randon numbers in
> Range B10:G15, then select the latter range, ener Conditional
> Formatting fro mthe Format menu. Choose "Formula Is" and enter :
>
> "=IF(COUNTIF($B$2:$G$2,G15)>=1,TRUE,FALSE)" (no quotes)
>
> then select the format you want to apply when the number is found.
> 


0
1/24/2006 9:23:17 PM
You don't need the IF in CF

=COUNTIF($B$2:$F$2;B10)>0 for cell b10

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Jean-Paul Hahn" <jean-paul.hahn@gmx.net> wrote in message
news:dr65s9$5l0$1@news.hispeed.ch...
> Thank you very much for your tip. I had to adjust the formula to
>
> =IF(COUNTIF($B$2:$F$2;B10)>0;TRUE;FALSE) for cell b10
> =IF(COUNTIF($B$2:$F$2;C10)>0;TRUE;FALSE) for cell c10
> =IF(COUNTIF($B$2:$F$2;D10)>0;TRUE;FALSE) for cell d10
> =IF(COUNTIF($B$2:$F$2;E10)>0;TRUE;FALSE) for cell e10
> =IF(COUNTIF($B$2:$F$2;F10)>0;TRUE;FALSE) for cell f10
>
> and then copy/paste special, format to the other cells below.
>
> Now everything works fine and I can quickly check wheter my numbers won in
> the lottery.
>
>
> <mrjsoftware@hotmail.com> schrieb im Newsbeitrag
> news:1138054102.996188.208610@o13g2000cwo.googlegroups.com...
> > Use the COUNTIF() Function in Conditional formatting.
> >
> > If the 6 numbers are in Range B2:F2 and you have randon numbers in
> > Range B10:G15, then select the latter range, ener Conditional
> > Formatting fro mthe Format menu. Choose "Formula Is" and enter :
> >
> > "=IF(COUNTIF($B$2:$G$2,G15)>=1,TRUE,FALSE)" (no quotes)
> >
> > then select the format you want to apply when the number is found.
> >
>
>


0
bob.phillips1 (6510)
1/24/2006 9:27:50 PM
Thanks Bob

You are right. Somehow, when I first tried it out it only worked with the IF 
clause included. I don't understand why it didn't work correctly in the 
first trial.

Jean-Paul

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> schrieb im Newsbeitrag 
news:%23kxRJ0SIGHA.3904@TK2MSFTNGP10.phx.gbl...
> You don't need the IF in CF
>
> =COUNTIF($B$2:$F$2;B10)>0 for cell b10
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Jean-Paul Hahn" <jean-paul.hahn@gmx.net> wrote in message
> news:dr65s9$5l0$1@news.hispeed.ch...
>> Thank you very much for your tip. I had to adjust the formula to
>>
>> =IF(COUNTIF($B$2:$F$2;B10)>0;TRUE;FALSE) for cell b10
>> =IF(COUNTIF($B$2:$F$2;C10)>0;TRUE;FALSE) for cell c10
>> =IF(COUNTIF($B$2:$F$2;D10)>0;TRUE;FALSE) for cell d10
>> =IF(COUNTIF($B$2:$F$2;E10)>0;TRUE;FALSE) for cell e10
>> =IF(COUNTIF($B$2:$F$2;F10)>0;TRUE;FALSE) for cell f10
>>
>> and then copy/paste special, format to the other cells below.
>>
>> Now everything works fine and I can quickly check wheter my numbers won 
>> in
>> the lottery.
>>
>>
>> <mrjsoftware@hotmail.com> schrieb im Newsbeitrag
>> news:1138054102.996188.208610@o13g2000cwo.googlegroups.com...
>> > Use the COUNTIF() Function in Conditional formatting.
>> >
>> > If the 6 numbers are in Range B2:F2 and you have randon numbers in
>> > Range B10:G15, then select the latter range, ener Conditional
>> > Formatting fro mthe Format menu. Choose "Formula Is" and enter :
>> >
>> > "=IF(COUNTIF($B$2:$G$2,G15)>=1,TRUE,FALSE)" (no quotes)
>> >
>> > then select the format you want to apply when the number is found.
>> >
>>
>>
>
> 


0
1/24/2006 9:37:06 PM
Reply:

Similar Artilces:

Excel 2007: Conditional formatting
In Excel 2007, I want to create a rule that turns cells red in case that 3 out of 7 cells in a column equal 0. What formula will catch this condition? Thanks to the math stars! Sven I did this with A1:G1 Selected the range; used Conditional Formatting on the Home group ->New Rule ->Use a formula ...... and entered this =COUNT($A$1:$G$1,0)>=3 When three or more cells in the range are zero (blanks do not count as zero here), all cells go red best wishes from Canada -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sven Berg" <...

legend formatting
Is there any way I can have subscript/superscripts in the legend of a chart? If it's not readily available, can a VBA script take care of it? Maynard - Unfortunately it is not possible to format individual characters of a legend entry or an axis tick label. You could make a textbox, which allows individual character formatting. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Maynard wrote: > Is there any way I can have subscript/superscripts in the legend of a > chart? If it's not readily available, c...

2003 condition
I have a numeric field with values 1,2 or 3 referenced by a label in an array formula. I thought that previous formula where condition LABEL=1 worked, but it now it has to be LABEL="1". Unfortunely it's in a file not frequently used so I can't prove that LABEL=1 ever work. So I was wondering if anyone knows if there has been a SP to Excel 2003 that might now require quotes for a conditional check of a numberic field or is my memory bad and this never should have worked in the first place. Thanks in advance for any insight ...

Auto Hide/Unhide Formatting
I'm wanting to create conditional formatting such that when users submit info in one cell, a previously hidden row underneath it becomes unhidden and available for formatting. Is there a way to do that? Not using Conditional Formatting. You would need event code to achieve the unhiding of a row. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False If Target.Value <> "" Then Rows("2:2").EntireRow.Hidden = False End If endit: Applic...

vlookup #3
What is the difference between a sliding scale vlookup and a fixed scale vlookup and which one is the best? Emelda, Not sure what you mean by "sliding scale" vs "fixed scale" but I'll take a guess. The fourth argument of a vlookup can be set to either True or False. VLookup("what", "where", "index", T/F) With the argument set to "True" (sliding scale???), your data table is assumed to be (and definitely should be) in alphanumeric order. Example setup: 0 A 100 B 200 C 300 D etc. A Vlookup of anything from 200 ...

How do I set up the default format for a comment?
I wan't all new excel comments to have the same look by default without having to edit each comment individually. I'm using Office 2003. I thought all the comments would have the same look. But if they don't, Debra Dalgleish has lots of code to format comments when you add them: http://www.contextures.com/xlcomments03.html#Formatted http://www.contextures.com/xlcomments03.html#Plain Gitesh wrote: > > I wan't all new excel comments to have the same look by default without > having to edit each comment individually. I'm using Office 2003. -- Dave Peterson ...

Printing Reports: Force A4 Format 07-19-07
hello! i am having trouble forcing a report to be in the A4 format when sending to the printer. it happens all the time that the report get send with the format 'letter' which causes the printer to stop and request for paper in letter format. the report i generate totally fits in the A4 format but it won't work the way i want it to! this is pretty annoying because i always have to manually push a button on the printer so that the report can be printed. is there a way through any preferences or vba code to make this work ? maybe someone can help me or give me a hint. thanks in adv...

Format of SSN Field on Form
I have a table with an pkEmpID (auto number) and txtSSN, txtLName, etc. fields. I have a form with a combo box used to query this table. The combo box retrieves the pkEmpID and txtSSN when the user types in the SSN. (The pkEmpID is bound to a field in another table.) When I type in the SSN, I want the combo box to display it as @@@-@@-@@@@. If I set the field format to @@@-@@-@@@@ in the table, the SSN displays the data okay on the form. But you have to type in the dashes as part of the entry. I prefer not to have to do that. I took the formatting off the table field and tried to s...

Reprint Large format
Does anyone know how I can reprint a Receipt into a different style. I printed a receipt through a 40 column printer and receipt printer two has a 80 column printer that printed the large format receipt i want... I would like to be able to print that large format receipt one more time. You have to have Printer #2 set to Journal Receipts ( in Register Properties). If this is not set, you won't be able to reprint. If it is, you can just open the Journal viewer and page up/page down to find the version you want. -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@...

multiple font formatting to a single cell
Hello, I am trying to use superscript in a cell to just one number not the entire text in that cell. I can format it ok, and the formatting is there. However, once I hit the enter key, the formatting to the one number disappears. I'm new to xp's version of excel. I used to be able to do it in earlier versions of excel. Example: salary 2. I only want to add superscript to the number 2. Is there a way to do this in xp? Thanks ...

help with header row format
Hi, The first row in my excel file is the header. When I insert a new row after that, the new row takes the format, color etc of the header. How can I avoid the new rows inserted form taking the format of the forst row? Thanks If it's Excel 2003, when you insert the new row under the header row youy should get a small icon appearing which has a drop down arrow providing format options. You can select to format the new row as the line above, the line below, or clear formatting. -- Noel O'K "Chris" wrote: > Hi, > The first row in my excel file is the header. W...

formating within formula
Hi group. In C10 cell I have the following formula: ="The total percentage is " & A10 Cell A10 is a percentage calculation. The problem I have is that in Cell C10, the display is something like this: The total percentage is 0.63447676223746 I know that if this value was a currency, I could have used the formula ="the total percentage is " & dollar(A10). This would have gotten rid of extra decimals. What other formating can I use to only keep two digit in this percentage within the formula? Any help is appreciated Thanks Hi try ="The total percenta...

Format Painter in MS Word
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Hi - How do I set up a keyboard short-cut for Format Painter? (MS Word 2008 for Mac version 12.1.7) Thanks Derek Go into Tools> Customize Keyboard & Menus then click the Keyboard button. On the Commands page select Edit from the Categories list then locate the following commands & assign the keystroke of choice to each: CopyFormat PasteFormat HTH |:>) Bob Jones [MVP] Office:Mac On 5/12/09 10:44 AM, in article 59b744e3.-1@webcrossing.caR9absDaxw, "Derek100@officeformac.com" <Derek100@o...

Link; clipboard format
I would like to extract the executable file name from an icon on the desktop. I've copied a link to the clipboard but it seems to have a non-standard clipboard format. Does anyone know how I can do this or where info on this format might be? thanks It is a shell clipboard format, so you have to read about the shell extensions. There is a special clipboard format in the shell for this. It has been enough months since I did this that I am at this point fairly clueless (I have decided that Shell programming, like ActiveX, is a Place I Do Not Wish To Go). But perhaps someone who understands...

Formatting numbers
How do I format numbers such that they appear like below: On selecting the "," icon, format the numbers in this way 100 to become 100.00 and if it is negative then (100.00). Likewise on selecting the "$" icon then display the number in this way 100 to become $100.00 and if it is negative then $(100.00). The location of the dollar sign should line up down the column irrespective of the length of the amount. I don't know why Excel doesn't do this by default. How do I then get Excel to always do this formatting by default. Cheers Mark Experiment with Format ...

Field validation and formatting
How do I validate and format a field? I want to format all phone numbers to a standard format--(###) ###-#### x###. However, for standard text boxes, I can not find any event tabs. All I can find is the onchange evend for pick lists. Thanks in advance! I saw this discussed several times that its not modifable. We run CRM in house but im not the CRM admin so I never verified it I just took the answer for what its worth. "Paul McBride" <pjmcb@comcast.net> wrote in news:#ECB0l9hEHA.1096@TK2MSFTNGP10.phx.gbl: > How do I validate and format a field? I want to format all...

Using vlookup against blank cell
I am using vlookup to return values (lets say values A-I) based on other values (Values 1-10) in column A. Sometimes there is no value in column A and I'd like vlookup to return a value "blank cell" or whatever I define. Right now blank cells return N/A. I am guessing there is something simply I can do there. Thanks in advance bmac One way: =IF(ISNA(MATCH(J1,A:A,FALSE)),"blank cell",VLOOKUP(J1,A:B,2,FALSE)) In article <#2YO5vFFIHA.4752@TK2MSFTNGP04.phx.gbl>, "bam" <bartmacl@yahoo.com> wrote: > I am using vlookup to return values (...

Conditional formatting 02-11-10
Hi In a cell, I want to allow the value only if a condition is satisfied. If this condition is not satisfied, and if I format the value to be white, then the value is still in the cell and cannot been seen because it is white. This is no good. I want no value in the cell. Any help Louis You can use Data Validation to prevent the entry of an invalid value. Or you could use event code to delete the invalid value after entry. Which way to go is up to you. Gord Dibben MS Excel MVP On Thu, 11 Feb 2010 07:35:01 -0800, LP <LP@discussions.microsoft.com> wrote: ...

Conditional Formatting in Excel Help Please.....
Newbie to VBA I need to use Conditional Formatting to format a range of cells to have Wheat background and Red text (Bold) if a text string is contained in each cell. For example, all cells in a range that contain the text string "LLC" or "Inc" or "Corp" or "Corporation" or "Company" , etc. If cell E2 contains Acme, LLC I need to give that cell (E2) the condition formatting If cell E3 contains John Brown That cell does not get the condition formatting If cell E4 contains Joe's Pool Hall, Inc Cell E4 gets the condition formatting Thanks ...

Wait condition and custom workflow stay waiting (error)
I have a custom workflow that update a field in a custom entity and it works fine but I need to delay its execution and for that I used a wait condition previous a this custom workflow. When the workflow (with a wait condition) exceute stay waiting and throw an error and in the table Workflowlog in the database show me the error -2147220970 (System.Runtime.Serialization.SerializationException: Type 'System.ComponentModel.Container' ..... ) Anyone can help me with this issue ? -- Pablo Choy Analyst Developer A full error message and detail information about the workflow defin...

Hindi Number format
Is ther an equivalent in Access for a for a number format available in Excel .NumberFormat = "[$-2000000]#0,000.00" So that I can have Hindi numerals in a reports. when I use Numeral shapes to context or system or National it is not giving me the desired result. I want to display the numbers in Hindi format regardless of the system language settings. How I can apply this in a Report so that the field will be formated using the above way? Thanks ...

Formatting toolbar won't stay active
I have Windows XP with Office XP, Outlook 2002. When I reply or forward an e-mail, I select view, toolbars, advanced and use the formatting buttons in the e-mail. However, when I reply or forward the next e-mail, the toolbar has disappeared. Any ideas on what might cause this or how can I get the tool bar to stay active when I reply or forward e-mails? "David" <david.king@hq.doe.gov> wrote in message news:03c201c365c7$5e440840$a401280a@phx.gbl > I have Windows XP with Office XP, Outlook 2002. When I > reply or forward an e-mail, I select view, toolbars, > adv...

Converting time formatted cells
Hi, I would like to convert time/hour formatted cells to days. For example: 11:47:00 17:12:00 to 1 day, 4 hours, 59 minutes How can I do this? Basically I am trying to add up time formatted cells so I can calculat the total number of days, hours and minutes from, for example cell C to C31. I am using Excel 2003 v11. Thanks : -- Message posted from http://www.ExcelForum.com I'm drawing a blank on how to do this with a shorter formula, but this should do: =INT(SUM(C2:C31))&" days, "&INT((SUM(C2:C31)-INT(SUM (C2:C31)))*24)&" hours, "&ROUND(((S...

Format painting bars in a chart
I have a funky problem..not sure why. Let's say I create a bar chart, and format each of the bars with a nice set of gradient colors, that varies based upon the data series source. In other words, several different color bars, on standard color per series. Now, if I happen to "touch" the data behind the chart in any way (in my case, a pivot table), the data element I change seems to go back to a "default" color scheme. I still have other bars that look like I want, but say, one month bar, for one data series, now looks different than the other 11 for the year...

Index fields and formatting
Hi I've inherited a document and some of the index fields - XE - are formatted as bold and I want to remove the formatting. I found a similar post going back to 2005 and the answer then was use paragraph formatting. I have Word 2003 and 2007 and just wondering if this is still the case. Also, is there a way to bold the text without bolding the index field. Bolding the line with fields switched off still bolds the field. Many thanks Ian To change the formatting applied to the XE (index entry) fields, you can make use of Find and Replace. Display the Find and Repla...