Function/Formatting

Have  a spreadsheet with a range of numbers(for ref the last 30 lotter
draws) in cells A1:g31.   What I would like is - if a number is entere
in say cell m2, a formula or conditional format that would highligh
that number in the range.  Is it possible?

Thanking everyone in advance.  Scap

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/28/2003 6:47:52 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
544 Views

Similar Articles

[PageSpeed] 5

One way, select A1:G31 with A1 as the active cell (if you start selecting
from A1 it will be active)
or select it by typing A1:A31 in the name box above the row headers
Now do format conditional formatting, formula is

=A1=$M$2

click the format button and select patterns and maybe a red colour
click OK twice

-- 

Regards,

Peo Sjoblom

"Scapa" <Scapa.xmapa@excelforum-nospam.com> wrote in message
news:Scapa.xmapa@excelforum-nospam.com...
>
> Have  a spreadsheet with a range of numbers(for ref the last 30 lottery
> draws) in cells A1:g31.   What I would like is - if a number is entered
> in say cell m2, a formula or conditional format that would highlight
> that number in the range.  Is it possible?
>
> Thanking everyone in advance.  Scapa
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
terre081 (3244)
11/28/2003 7:01:13 PM
Scapa,

Yes it is with conditional formatting

Select A1:G31
Goto menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add the Formula =A1=$M$2
Click the format button
Select the pattern tab
Choose a colour
OK
OK

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Scapa" <Scapa.xmapa@excelforum-nospam.com> wrote in message
news:Scapa.xmapa@excelforum-nospam.com...
>
> Have  a spreadsheet with a range of numbers(for ref the last 30 lottery
> draws) in cells A1:g31.   What I would like is - if a number is entered
> in say cell m2, a formula or conditional format that would highlight
> that number in the range.  Is it possible?
>
> Thanking everyone in advance.  Scapa
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
11/28/2003 7:07:37 PM
Reply:

Similar Artilces:

Number Formatting #3
Thank you, now at least it makes sense! :) -- Nan ------------------------------------------------------------------------ Nan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6785 View this thread: http://www.excelforum.com/showthread.php?threadid=272052 You're welcome. See the thread started by M.Johnson about rounding. You might find something of use to you there too. Carlos "Nan" <Nan.1eop7y@excelforum-nospam.com> wrote in message news:Nan.1eop7y@excelforum-nospam.com... > > Thank you, now at least it makes sense! :) > &...

nunber format in Excel 2000
I am looking for a way by using the "format cell,number,custom" to pad a cell with a predetermined amount of trailing zero's and not leading zero's. All I can do is get leading zero's. Thanks loads Erle try #,###0.000000 -- Don Guillett SalesAid Software donaldb@281.com "Erle" <e-d.marans@ari.co.il> wrote in message news:972c2309.0411301104.64860515@posting.google.com... > I am looking for a way by using the "format cell,number,custom" to pad > a cell with a predetermined amount of trailing zero's and not leading > zero's. A...

Date/Text Formatting
Hi, I have a column with in date format and would like to get this into a text format as follows : Column would say : 01/03/04 I want text to say : 'The month is March' Any help would be appreciated... Thanks Tappie One way .. Assuming dates are in col A, A1 down Put in B1: ="The month is "&CHOOSE(MONTH(A1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oc t","Nov","Dec") Copy down -- Rgds Max xl 97 --- Please respond in thread xdeme...

How Draw a chart showing the following function: y=sin(x)
Can someone explain me how can i make this chart i excel? Remember i don't want use function to count! I want draw! Thanks for hel -- Pusie ----------------------------------------------------------------------- PusieK's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1621 View this thread: http://www.excelforum.com/showthread.php?threadid=27630 For a variety of approaches adapt the ideas at Normal curve http://www.tushar-mehta.com/excel/charts/normal_distribution/index.htm particularly the three sections: Enumerating a set of points in a worksheet Using...

How do I hide zero values but keep currency format in P.O. form?
I'm creating a purchase order for our company and am having trouble formatting some cells. I need a currency value in the Total section only if there is a value in the Quantity and Unit Price section. I formatted the Total cells to Currency. But if there are no values in Quantity or Unit Price, zero values appear in the Total cells. How do I format the total cells to only show price values when there are values in Quantity and Unit Price? When you format a cell as currency you get a custom format like this (depending on the otpions you chose)... $#,##0.00;[Red]$#,##0.00 Choose the ...

Default Page Format set to custom, can't change.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel About six months ago I created a custom page setup in Word (4x7 inch). Now excel is always opening new documents in this custom format. I've gone into page setup and changed it, then saved it... nothing. I've checked the settings in the printer dialog under system preferences... all fine. <br><br>Any ideas how to get this back to opening in the standard letter size? Is your installation of Office fully updated? 12.2.3 is the current level & I seem to recall an issue several revisions ago wh...

format cell
how to enter a line feed into a cell? want to enter like the following into one cell this is a test.(CR LF) this is a test instead of this is a test. this is a test ...

"Too many formats used"!!!
I have a large spreadsheet with multiple input forms which is almost complete. I am unable to progress further as Excel will not allow me to format any more cells with a "Too many formats used" message being displayed I understand that I need to reduce the amount of formatting that has been used, but my endeavours to do so simply pops up the same message. Is there a simple way to reduce the number of formats used in a spreadsheet either globally or per worksheet? Or any other solutions to this problem Any help would be greatfully appreciated. Hi for dealing with too many cell for...

IRR function
When we calculate an IRR using the IRR function with yearly and monthly cash flow payments we are getting very different results. The monthly payments sum up to the yearly payments. Any insight would be appreciated. Since you don't use dates with IRR, it calculates the return *per period*, i.e. monthly rate if you enter monthly cash flows, annual rate if you enter only 1 cash flow per year. Are you taking that into account? On Tue, 28 Sep 2004 06:01:27 -0700, <anonymous@discussions.microsoft.com> wrote: >When we calculate an IRR using the IRR function with >yearly and...

Pasting Access table into Excel; can't use SUM function
We have 200+ Oracle database tables that we use to create queries and make tables. We create select queries on a daily basis that are comprised of approx. 8,000 to 14,000 records. We usually take this information and paste it into Excel for analysis. Recently, we upgraded from NT 4.0 WS to XP Pro. We noticed that when we now paste our information into excel, the formatting is changed. Numbers that we could easily SUM before, no longer respond to the sum function. When we use the SUM function, the function does not recognize the cell as a number and responds with '0'. When...

Send From function in Outlook 2003
Hi All, Wondered if anyone could help with this? I have my own Exchange account in Outlook for my personal emails, and I also have another user account setup to send and receive mail. Now is there any way of sending mail from the other account using my personal exchange account. I have tried using the FROM field in a new message but doesnt seem to work. And I also need it not to show "on behalf of", so no-one would know it has come from the other account? Any ideas? the administrator needs to give you send as permission on the personal account in ADUC. "cruwyser" ...

Excel graphs should allow copy & paste of individual bar formats
In excel graphs - bar charts, I make the last bar a hashed shading as it is a forcast. To do this I select only the last bar and change its formating. At the end of the month I want to change the format back to the same as all the other bars and make the new coming month's predicted value that hashed shading etc. Unfortunately excel will not allow copy and paste in this situation - so I have do open the format tab etc and then change. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggest...

Why color formatting not showing on worksheet?
I have a color printer and preview is in color. On-screen worksheet shows only in black and white. What can be done to fix this? Hi! Take a look at this: http://contextures.com/xlfaqApp.html#Fill Biff >-----Original Message----- >I have a color printer and preview is in color. On- screen worksheet shows >only in black and white. What can be done to fix this? >. > Look at this KB article http://support.microsoft.com/d´┐Żefault.aspx?kbid=320531 Changes to fill color and fill pattern are not displayed in Office programs -- HTH RP (remove nothere from the email addres...

FRx row format account labels
Hello: When an FRx row format is created by using "Edit...Add Rows from Chart of Accounts" and so forth, how does FRx determine the name of the account to place within the row? What I'm getting at is that a row format that is based on the natural account segment can represent several accounts--for example, an account that represents several departments--can be "divided" into different departments based on the segment of the account. So, for an account that has as many as ten departments, how does FRx determine the name for the account. The account description ...

convert into time format
hi I want to make a simple timetabel for my employees. Instead of time format mm:ss I would like them just to type 1000 as in 10:00. I cannot find the right way, and do not have expert skills in Excel. pls. help. Henrik. Cannot be done in Excel simply by formatting the cells. You would have to use an adjacent column as a helper column with formulas to make the change to time. Chip Pearson has event code for quick entry of dates/times. http://www.cpearson.com/excel/DateTimeEntry.htm Or an add-in from Bob Phillips' site http://www.xldynamic.com/source/xld.QDEDownload.html Gord Dibb...

Chart with conditional formatting
I have a spreadsheet that I am using to keep track of the RSVPs to a banquet. The spreadsheet has the standard columns such as first name, last name, company etc, I have also added columns for table # (the table I have assigned them for seating at the banquet), a Y/N column to indicate if we have received payment for the banquet and a Y/N column that shows if they are a VIP or Speaker. What I would like to do is create a chart using conditional formatting in worksheet cells. I envision having a row for each table number, then a column that would represent each seat at the table (each ta...

copy and paste formatting
Hi I am copying and pasting individual files - over 100 of them into 1 file. Each time I paste a new one in the file it keeps it formatting rather than conforming to the format of the file it is being pasted into (ei font type and size and margin settings). Is there a way to paste it so that it automatically changes the format, rather than keeping what it was in the original file? -- Thank-you! Ruth Use Paste Special > Paste Unformatted. On Mar 17, 9:51=A0am, Ruth <R...@discussions.microsoft.com> wrote: > Hi > > I am copying and pasting individu...

customizing chart column color formats in code
I have some code that examines a set of data and generates a x1columncluster chart. Depending on the set of data, I may have any number of column clusters as a result. However, I currently format the columns manually to make certain groupings a given color and another group of columns a different color and so forth. I would like to automate this. Is there a way to go through each column like for x = 1 to numberOfColumns check group If group = 1 set color accordingly else set color differently end if Did that make sense? TIA Perhaps you could use Jon Peltier's technique for conditio...

Database results format problem
Hi I have an issue with a field [priceeuros] that reports out as a sinle interger, no commas, can someone help please This is the second page of the DBRW I am experimenting with, but there is a syntax problem SELECT * FROM Apmt1bed, format([priceeuros],'Currency') as fmtamount, ORDER BY PriceEuros ASC I have 1234567 and want 1,234,567 thank you for your help See http://www.spiderwebwoman.com/resources/dbrwtipsandtricks.asp#currency If you don't want the $ format, just use Format(priceeuros,'#,##0.00') as fmtamount -- ______________________________...

how do i format the cells so that all the blanks show -
hi i want to format the cells so that all the blanks or zero number show a hyphon i.e one of these - thanks : -- Message posted from http://www.ExcelForum.com XL's format convention has 4 sections separated by semicolons. By default these sections are for positive numbers, negative numbers, zero, and text. To get what you want for zeros, choose Format/Cells/Number/Custom General;General;-;@ You can't format a blank cell to show a value (or then it isn't blank). Instead, enter a hyphen (or zero) in the cells you don't want another value in. In article <sokevin...

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

Time Format #6
I'm having a problem adding together journey times. I have a start time (say 8:34:00) and an end time (say 10:23:00) and from this I calculate a journey time (1:49:00). I then add this journey time (using the sum function) across a number of different journeys giving me a total. This total is in some case in excess of 24 hours and Excel seems to revert to 0 at 24 hours and start the sum again. So for example I can see when I highlight the range that the total is 27:49:00 but the sum function returns 03:49:00. Any ideas? Cheers Lee Use a custom format: [hh]:mm:ss The []...

Default Cell Format Hijacked to Scientific?
When I type in the number 12 on a new sheet I get: 1.2E+45 When I change the format to number I get: 1200000000000000000000000000000000000000000000.00 I can't seem to fix this. Help!! John Elfrank-Dana Social Studies Teacher/Web Master Murry Bergtraum High School john@elfrank.org http://www.elfrank.org Look under tools>options>edit and uncheck fixed decimal places -- Regards, Peo Sjoblom "john elf" <john@elfrank.org> wrote in message news:052201c3a020$6eaa0660$a301280a@phx.gbl... > When I type in the number 12 on a new sheet I get: 1.2E+45 > When I chan...

Conditional Formatting Error
Appreciate it if some experts could tell me what wrong with m conditional formatting (CF) formula. In Col A, I have dates (se example below). I want the current week to be highlighted with different color. My current CF for A1 cell is something like this:- =AND(WEEKNUM(A1)=WEEKNUM(NOW()),1). However, I encounter this error and Excel would not allow me t proceed. "You may not use references to other worksheets or workbooks fo Conditional Formatting criteria". I am not making any references to other worksheets or workbooks. Thanks. Col A [1] 3-May-04 [2] 10-May-04 <-- this...

Excel worksheet function
Can I protect my worksheet in Excel and enable the spell function... Only by using VBA macro code. Requires VBA to unprotect the sheet, do the spellcheck then reprotect the sheet. Similar to....... Sub Spell_Check() ActiveSheet.Unprotect Password:="justme" Cells.CheckSpelling SpellLang:=1033 ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstar...