Conditional Formatting - Lookup Range

Hello all, 

I have a spreadsheet with 79,000 cells of information in rows and
columns. On a separate worksheet I have 5,000 cells of information. 

I would like any information in the first sheet that is in the second
sheet to turn to red.

For example.  

Sheet1, column A
hellothere
howareyou
iamfine

Sheet2, column A
hellothere
iamfine

I would like the two strings in sheet one which appear in sheet 2 to
turn red, leaving the middle string black. 

Is there a way to do a lookup to compare the cell value to a range
using conditional formatting?

Thanks for any help.

Mxx


-- 
murphyz
------------------------------------------------------------------------
murphyz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20624
View this thread: http://www.excelforum.com/showthread.php?threadid=398508

0
8/24/2005 8:32:37 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
333 Views

Similar Articles

[PageSpeed] 7

There is.

Add a name for the range of data on sheet 2, say myRange

Select all the cells on Sheet1

Change the Condition 1 value in CF to Formula Is

Add a formula of =NOT(ISNA(MATCH(A1,myRange,0)))

Click Format, select pattern and choose red

OK out

-- 
 HTH

Bob Phillips

"murphyz" <murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com> wrote in
message news:murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com...
>
> Hello all,
>
> I have a spreadsheet with 79,000 cells of information in rows and
> columns. On a separate worksheet I have 5,000 cells of information.
>
> I would like any information in the first sheet that is in the second
> sheet to turn to red.
>
> For example.
>
> Sheet1, column A
> hellothere
> howareyou
> iamfine
>
> Sheet2, column A
> hellothere
> iamfine
>
> I would like the two strings in sheet one which appear in sheet 2 to
> turn red, leaving the middle string black.
>
> Is there a way to do a lookup to compare the cell value to a range
> using conditional formatting?
>
> Thanks for any help.
>
> Mxx
>
>
> -- 
> murphyz
> ------------------------------------------------------------------------
> murphyz's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=20624
> View this thread: http://www.excelforum.com/showthread.php?threadid=398508
>


0
bob.phillips1 (6510)
8/24/2005 10:24:33 AM
Try this code. Is it what you wanted to do? Sorry if I misunderstood
you.

Sub RedIfExist()

Dim rngCell As Range    'cells
Dim rngWork As Range    'working range
Dim lngLastRow  As Long     'last row
Dim lngLastRow1 As Long     'last row in 1st sheet
Dim ws2ndSheet  As Worksheet    '2nd worksheet
Dim lngRow  As Long     'row in 2nd sheet

'find last row in 1st sheet
lngLastRow1 = Cells(Rows.Count, "A").End(xlUp).Row

'"Sheet2" is the name of your 2nd sheet
Set ws2ndSheet = Sheets("Sheet2")
'Last row in 2nd sheet assuming column A always contains data
lngLastRow = ws2ndSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Your 1st sheet date range
Set rngWork = Range("A1:A" & lngLastRow1)

'This will loop in working range
For Each rngCell In rngWork
'loop in the 2nd sheet until last row
For lngRow = 1 To lngLastRow
'If current cell value = column A in 2nd sheet value
'color red to the cell
If rngCell.Value = ws2ndSheet.Cells(lngRow, "A").Value Then
rngCell.Interior.ColorIndex = 3
End If
Next
Next

End Sub


-- 
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18903
View this thread: http://www.excelforum.com/showthread.php?threadid=398508

0
8/24/2005 10:50:41 AM
Bob,
Does Match() only work where you are comparing 1 column (sheet1) against 
another single column(sheet2).  I tried entering data on sheet2 in columns 
b, c, and d (also including in my "myrange"), but nothing turns red now on 
my sheet1.
TIA,
Jim

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:uF%23$SYJqFHA.3108@TK2MSFTNGP12.phx.gbl...
> There is.
>
> Add a name for the range of data on sheet 2, say myRange
>
> Select all the cells on Sheet1
>
> Change the Condition 1 value in CF to Formula Is
>
> Add a formula of =NOT(ISNA(MATCH(A1,myRange,0)))
>
> Click Format, select pattern and choose red
>
> OK out
>
> -- 
> HTH
>
> Bob Phillips
>
> "murphyz" <murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com> wrote in
> message news:murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com...
>>
>> Hello all,
>>
>> I have a spreadsheet with 79,000 cells of information in rows and
>> columns. On a separate worksheet I have 5,000 cells of information.
>>
>> I would like any information in the first sheet that is in the second
>> sheet to turn to red.
>>
>> For example.
>>
>> Sheet1, column A
>> hellothere
>> howareyou
>> iamfine
>>
>> Sheet2, column A
>> hellothere
>> iamfine
>>
>> I would like the two strings in sheet one which appear in sheet 2 to
>> turn red, leaving the middle string black.
>>
>> Is there a way to do a lookup to compare the cell value to a range
>> using conditional formatting?
>>
>> Thanks for any help.
>>
>> Mxx
>>
>>
>> -- 
>> murphyz
>> ------------------------------------------------------------------------
>> murphyz's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=20624
>> View this thread: 
>> http://www.excelforum.com/showthread.php?threadid=398508
>>
>
> 


0
jmay (696)
8/24/2005 11:16:28 AM
Hi Jim,

Yes, MATCH is single column/row, it returns an index into that array, so if
it were multi-column and row, it would need to return two values.

-- 
 HTH

Bob Phillips

"Jim May" <jmay@cox.net> wrote in message news:iGYOe.432$8q.32@lakeread01...
> Bob,
> Does Match() only work where you are comparing 1 column (sheet1) against
> another single column(sheet2).  I tried entering data on sheet2 in columns
> b, c, and d (also including in my "myrange"), but nothing turns red now on
> my sheet1.
> TIA,
> Jim
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:uF%23$SYJqFHA.3108@TK2MSFTNGP12.phx.gbl...
> > There is.
> >
> > Add a name for the range of data on sheet 2, say myRange
> >
> > Select all the cells on Sheet1
> >
> > Change the Condition 1 value in CF to Formula Is
> >
> > Add a formula of =NOT(ISNA(MATCH(A1,myRange,0)))
> >
> > Click Format, select pattern and choose red
> >
> > OK out
> >
> > -- 
> > HTH
> >
> > Bob Phillips
> >
> > "murphyz" <murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com> wrote
in
> > message news:murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com...
> >>
> >> Hello all,
> >>
> >> I have a spreadsheet with 79,000 cells of information in rows and
> >> columns. On a separate worksheet I have 5,000 cells of information.
> >>
> >> I would like any information in the first sheet that is in the second
> >> sheet to turn to red.
> >>
> >> For example.
> >>
> >> Sheet1, column A
> >> hellothere
> >> howareyou
> >> iamfine
> >>
> >> Sheet2, column A
> >> hellothere
> >> iamfine
> >>
> >> I would like the two strings in sheet one which appear in sheet 2 to
> >> turn red, leaving the middle string black.
> >>
> >> Is there a way to do a lookup to compare the cell value to a range
> >> using conditional formatting?
> >>
> >> Thanks for any help.
> >>
> >> Mxx
> >>
> >>
> >> -- 
> >> murphyz
>
>> ------------------------------------------------------------------------
> >> murphyz's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=20624
> >> View this thread:
> >> http://www.excelforum.com/showthread.php?threadid=398508
> >>
> >
> >
>
>


0
bob.phillips1 (6510)
8/24/2005 11:23:14 AM
"so if it were multi-column and row, it would need to return two values."  < 
WHICH IT CAN'T !! Right?
Sorry to be so thick (here),,
Appreciate your contribution to this (an other excel) group(s).
Jim

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:%23qCsN5JqFHA.1272@TK2MSFTNGP11.phx.gbl...
> Hi Jim,
>
> Yes, MATCH is single column/row, it returns an index into that array, so 
> if
> it were multi-column and row, it would need to return two values.
>
> -- 
> HTH
>
> Bob Phillips
>
> "Jim May" <jmay@cox.net> wrote in message 
> news:iGYOe.432$8q.32@lakeread01...
>> Bob,
>> Does Match() only work where you are comparing 1 column (sheet1) against
>> another single column(sheet2).  I tried entering data on sheet2 in 
>> columns
>> b, c, and d (also including in my "myrange"), but nothing turns red now 
>> on
>> my sheet1.
>> TIA,
>> Jim
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:uF%23$SYJqFHA.3108@TK2MSFTNGP12.phx.gbl...
>> > There is.
>> >
>> > Add a name for the range of data on sheet 2, say myRange
>> >
>> > Select all the cells on Sheet1
>> >
>> > Change the Condition 1 value in CF to Formula Is
>> >
>> > Add a formula of =NOT(ISNA(MATCH(A1,myRange,0)))
>> >
>> > Click Format, select pattern and choose red
>> >
>> > OK out
>> >
>> > -- 
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "murphyz" <murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com> wrote
> in
>> > message news:murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com...
>> >>
>> >> Hello all,
>> >>
>> >> I have a spreadsheet with 79,000 cells of information in rows and
>> >> columns. On a separate worksheet I have 5,000 cells of information.
>> >>
>> >> I would like any information in the first sheet that is in the second
>> >> sheet to turn to red.
>> >>
>> >> For example.
>> >>
>> >> Sheet1, column A
>> >> hellothere
>> >> howareyou
>> >> iamfine
>> >>
>> >> Sheet2, column A
>> >> hellothere
>> >> iamfine
>> >>
>> >> I would like the two strings in sheet one which appear in sheet 2 to
>> >> turn red, leaving the middle string black.
>> >>
>> >> Is there a way to do a lookup to compare the cell value to a range
>> >> using conditional formatting?
>> >>
>> >> Thanks for any help.
>> >>
>> >> Mxx
>> >>
>> >>
>> >> -- 
>> >> murphyz
>>
>>> ------------------------------------------------------------------------
>> >> murphyz's Profile:
>> > http://www.excelforum.com/member.php?action=getinfo&userid=20624
>> >> View this thread:
>> >> http://www.excelforum.com/showthread.php?threadid=398508
>> >>
>> >
>> >
>>
>>
>
> 


0
jmay (696)
8/24/2005 11:44:29 AM
I hesitate to say it can't (or more accurately couldn't), but it doesn't.

Bob

"Jim May" <jmay@cox.net> wrote in message
news:z4ZOe.434$8q.266@lakeread01...
> "so if it were multi-column and row, it would need to return two values."
<
> WHICH IT CAN'T !! Right?
> Sorry to be so thick (here),,
> Appreciate your contribution to this (an other excel) group(s).
> Jim
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23qCsN5JqFHA.1272@TK2MSFTNGP11.phx.gbl...
> > Hi Jim,
> >
> > Yes, MATCH is single column/row, it returns an index into that array, so
> > if
> > it were multi-column and row, it would need to return two values.
> >
> > -- 
> > HTH
> >
> > Bob Phillips
> >
> > "Jim May" <jmay@cox.net> wrote in message
> > news:iGYOe.432$8q.32@lakeread01...
> >> Bob,
> >> Does Match() only work where you are comparing 1 column (sheet1)
against
> >> another single column(sheet2).  I tried entering data on sheet2 in
> >> columns
> >> b, c, and d (also including in my "myrange"), but nothing turns red now
> >> on
> >> my sheet1.
> >> TIA,
> >> Jim
> >>
> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> >> news:uF%23$SYJqFHA.3108@TK2MSFTNGP12.phx.gbl...
> >> > There is.
> >> >
> >> > Add a name for the range of data on sheet 2, say myRange
> >> >
> >> > Select all the cells on Sheet1
> >> >
> >> > Change the Condition 1 value in CF to Formula Is
> >> >
> >> > Add a formula of =NOT(ISNA(MATCH(A1,myRange,0)))
> >> >
> >> > Click Format, select pattern and choose red
> >> >
> >> > OK out
> >> >
> >> > -- 
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > "murphyz" <murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com>
wrote
> > in
> >> > message news:murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com...
> >> >>
> >> >> Hello all,
> >> >>
> >> >> I have a spreadsheet with 79,000 cells of information in rows and
> >> >> columns. On a separate worksheet I have 5,000 cells of information.
> >> >>
> >> >> I would like any information in the first sheet that is in the
second
> >> >> sheet to turn to red.
> >> >>
> >> >> For example.
> >> >>
> >> >> Sheet1, column A
> >> >> hellothere
> >> >> howareyou
> >> >> iamfine
> >> >>
> >> >> Sheet2, column A
> >> >> hellothere
> >> >> iamfine
> >> >>
> >> >> I would like the two strings in sheet one which appear in sheet 2 to
> >> >> turn red, leaving the middle string black.
> >> >>
> >> >> Is there a way to do a lookup to compare the cell value to a range
> >> >> using conditional formatting?
> >> >>
> >> >> Thanks for any help.
> >> >>
> >> >> Mxx
> >> >>
> >> >>
> >> >> -- 
> >> >> murphyz
> >>
>
>>> ------------------------------------------------------------------------
> >> >> murphyz's Profile:
> >> > http://www.excelforum.com/member.php?action=getinfo&userid=20624
> >> >> View this thread:
> >> >> http://www.excelforum.com/showthread.php?threadid=398508
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>


0
bob.phillips1 (6510)
8/24/2005 11:55:24 AM
Another way:

Select the range of cells that contain the "master list".  Give it a nice range
name (Insert|Name|Define).  I used myRng.

Then back to the other sheet that should turn colors.

Select your range (and with A1 the active cell):

format|conditional formatting
Formula is:  =COUNTIF(myRng,A1)>0
And give it a nice format (from the pattern tab???)



murphyz wrote:
> 
> Hello all,
> 
> I have a spreadsheet with 79,000 cells of information in rows and
> columns. On a separate worksheet I have 5,000 cells of information.
> 
> I would like any information in the first sheet that is in the second
> sheet to turn to red.
> 
> For example.
> 
> Sheet1, column A
> hellothere
> howareyou
> iamfine
> 
> Sheet2, column A
> hellothere
> iamfine
> 
> I would like the two strings in sheet one which appear in sheet 2 to
> turn red, leaving the middle string black.
> 
> Is there a way to do a lookup to compare the cell value to a range
> using conditional formatting?
> 
> Thanks for any help.
> 
> Mxx
> 
> --
> murphyz
> ------------------------------------------------------------------------
> murphyz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20624
> View this thread: http://www.excelforum.com/showthread.php?threadid=398508

-- 

Dave Peterson
0
petersod (12004)
8/24/2005 1:51:44 PM
Thanks for all the answers.

I went with the way that Bob had mentioned and that worked fine and
dandy for what I needed. 

Much appreciated for all who answered though. 

Mxx


-- 
murphyz
------------------------------------------------------------------------
murphyz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20624
View this thread: http://www.excelforum.com/showthread.php?threadid=398508

0
8/24/2005 2:55:50 PM
Reply:

Similar Artilces:

Conditional format #12
Hi. I am trying to set a conditional format. If NETWORKDAYS(A1,TODAY())> 3 I would like the cell to format with red shading. How do is set the formula in the condtional statement? I tried ="NETWORKDAYS(A1,TODAY())>3" , but this doesn't seem to work. Thanks, Mike. Hi Mike! It doesn't work because the NETWORKDAYS function is in another workbook (the Analysis ToolPak add-in). You can use a workaround by putting your condition in a separate cell (eg B1) and then referring to that cell: B1 =NETWORKDAYS(A1,TODAY()) Then your conditional format for A1 becomes:...

Where is the Keep Text Formatting feature located in Word 07
I believe this Keep Text Formatting feature might be what I need, but I have been unable to locate exactly where it is located in Word 2007. I'm trying to rid a Word document sent to me of tables, text boxes, graphics and all other document formatting, while retaining the document's text content. It is unimportant to me whether the text formatting is retained or not. Thanks. Are you referring to a Keep Text Formatting feature in an earlier version of Word? I wonder whether what you're looking for is "Paste Unformatted," since you seem to be saying you _don...

formating color
hi i have a formating question. i have cells with number choices i them. for example the cell looks like this. 100, 105, 123, 041, 514, 455 now i want to go back and select one of these. i wanted to highligh the one that was chosen. but it will not let me. is there a way i ca do this? it will only allow me to highlight the entire cell, not jus parts of it. thank you in advanc -- RAPPE ----------------------------------------------------------------------- RAPPEL's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3624 View this thread: http://www.excelforum.com...

How can I customize the Filter Drop down on the formatting toolbar
I want to clear everything out of it, except the customized filters I have created. Can i do that? Project > Filter by > More Filters, then select each one, Edit, and remove the checkbox for Show in Menu. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I want to clear everything out of it, except the customized filters I > have created. Can i do that? > I've tried that - it doesn't affect that drop down filter list - whether they are checked or not. "Andrew Lavinsky" wrote: > Project > Filter by > Mo...

First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...

Formatting
I received a pub doc from a third party. It was formatted as a 11x16 size. Once I decreased it to 8.5x11 and view the doc under 'print preview' the entirety of the image did not view. Basically, the image didn't shrink with the size. Any input? Patty wrote: > I received a pub doc from a third party. It was formatted as a 11x16 size. > Once I decreased it to 8.5x11 and view the doc under 'print preview' the > entirety of the image did not view. Basically, the image didn't shrink with > the size. Any input? Publisher doesn't resize the contents o...

Export a range to a text file
Hello need some advise on how to procede I need to be able to create a text file containg some text as well as data that is within a named range in excel and then some more text. I can handle printing to the text files using cell values etc but am unsure of the best way to print the ranges data. Is there a way or procedure to just print the range as is in csv format? As well my range will contain about 6 columns, each containg a number field (formatting of decimal places is important, some have 2 dec some 3 etc) Also the range has a max of 50 rows however will always contain lower rows of...

Formatting multiple object types in output from a function?
I'm stumped on this one, and have searched and searched for a solution or how-to with no luck. Perhaps it just can't be done. I have a function that may or may not return objects of multiple types. I've setup the type and formatting information for each of the individual types in format.ps1xml files, and when only 1 type is returned by the function the formatting works as I would expect. However, if two or more types are returned by the function, only the formatting for the first object type is applied and then the other types are displayed however PoSH deems ap...

Conditional Format #20
I see that I can only use 3 conditional formats. I need a few more than 3 is there any way to do this. Kevin Try this free add-in on Bob Phillips' website: http://www.xldynamic.com/source/xld.CFPlus.Download.html Another possibility (albeit limited) is to combine your conditional formats with custom formatting. See J.E. McGimpsey's site for an example: http://www.mcgimpsey.com/excel/conditional6.html HTH Jason Atlanta, GA >-----Original Message----- >I see that I can only use 3 conditional formats. I need a few more than 3 is >there any way to do this. > >Kev...

Condition navigating between Controls
I have a form in access 2007, with several controls. I used the On Exit Event Procedure for navigating among controls. Example: If Method of Payment = “Check” Then Check No.Set Focus End If If Method of Payment = “Credit Card” Then Credit Card Name.Set Focus End If End Sub And so on for all controls. Form functioned well for months, however today went wild. Navigation order does not execute Event Procedure it follows exactly the order of TAB ORDER DIALOG BOX. Please advice -- Bardia It sounds like the On Exit event is not firing for some reason. Put these two lines of co...

LOOKUP Function #6
I discovered LOOKUP today and it worked fine until I made changes to the table of data. Now it's returning incorrect figures. Any suggestions? AC Sales wrote: > I discovered LOOKUP today and it worked fine until I made changes to the > table of data. Now it's returning incorrect figures. Any suggestions? --------- For LOOKUP() to work properly the data all has to be sorted into order first. Did you perhaps put an out of sequence value into the table? Bill ...

lookup row number
Hi, can you tell me how to lookup the row number of a name in a list. EG say I have a list of names as below: Andrew Mike Steve Ian Paul I would normally just add a column with the row numbers and then lookup the row number in the second column but is it possible to do without adding the extra column by using the row formula someohow? Thanks Hi Michael Try the below.. =MATCH("Mike",A:A,0) -- Jacob (MVP - Excel) "Michael" wrote: > Hi, > can you tell me how to lookup the row number of a name in a list. > > EG say I hav...

Conditional formatting / blank cells
Hello, I need help with a Conditional Format. This is my worksheet. Row 4 A B C D E F G H I $200 $210 I want a conditional format in G4 that states if G4 is greater than or equal to F4 the fill colour is green. If G4 is less than F4, the fill colour is red. If G4 is blank, the fill colour is white. I've tried numerous combinations, but cannot seem to get this to work. Thanks torkattack. Test for the blank first. -- David Biddulph "torkattack" <torkattack@discussions.microsoft.com> wrote ...

Excel could not save all the data and formatting you recently added
Hi, One of our users sent me an Excel file of 6 MiB. It has 7 worksheets. Most of them have <100 rows and AH columns, one sheet has 13160 rows and AH columns. The large sheet has autofilter enabled, but no actual filtering is done. (yet) 4 columns have validation: they allow a list of values specified in a range somewhere else in the sheet. There is also conditional formatting. It takes >30 seconds to calculate the sheet, however there are no real calculations, just a few concatenated string. My first impression is that this is yet another example of Excel (ab)used as a database. The p...

Table-lookup transformations
Hi, we are doing some table-lookup transformations based on LEFT and INNER joins. Probably around 100 - 50K rows/second or something like that. I have noticed that the lookups are getting slower and slower due to a growing database. The server itself is heavily loaded, CPU and IO-subsystem - which I know is not a good combination with SQL server :| I am looking for a way to increase the table-lookup transformation speed without mixing up things with SSIS.. Are there any good ways of doing table-lookup transformations within SQL server? Would it be a good idea to write s...

PDF format pagination
I have several reports utilizing Landscape layout. I followed the Report Writers Guide from the SDK with the Width at 11 and Height 8.25 or smaller. However, when I export it to the PDF format, the layout shows up Portrait. How could I change this? The export is fine in Excel with the correct margin setup. Thanks. ...

CSV, Milliseconds default formatting of mm:ss.0
When I generate some CSV files I need to include milliseconds. When anyone opens the file in Excel (in my case part of Office 2003, SP2) the datetimes appear with a default formatting of mm:ss.0. This is frustrating and appears unprofessional on my part when clients open the file. They assume I have made some sort of mistake. I have already thought of generating using =DATE(blah)+TIME(blah)+millis/(24*60*60*1000) but I don't like the idea. How can I change the default format so that it would be something more sensible and my clients and I could avoid the drudgery of clicking on the...

Conditional formatting formula with multiple criteria
Hi all, Have been puzzling over this for a while now, and can't get it right! Using conditional formatting, I want to: If any of the cells in Column B are blank, and the cells in Column C are blank, and the date in Column D is less than todays date - 3 days, turn the cell bold and red Can someone please help? I have tried all different variations and just can't get it! Thanks, Kirstie Kirstie Your questions sound like you wish to pick up whether there is a blank ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that the date in a single cell in colum...

Using conditions in WF Sales Processes
I'm creating a workflow process that I am trying to implement in MSCRM. The problem is that within each Stage, I have activities that should be dependent on the completion of the previous activity. The easiest way to solve this is to go beyond the 5 step Sales Process and create a new Stage for each successive activity. However, in the Sales Process tab this is quite unsightly. I've tried using a normal manual WF as a subprocess to the Sales Process WF. This works, but it gives me 2 problems: 1. The WF Subprocess does not appear in the Opportunity Sales Process Tab. The customer want...

Export to comma-delimited format doesn't seem to work
I apologize for the elementary question. I have an Excel spreadsheet which is simply a list of email addresses. I want to export them to a format allowing me to paste them into the "To" field of an email. (In case you're concerned, this is not unsolicited bulk commercial email). I've been able to export them to a .csv format, but it doesn't seem to have the commas, which confuses me and my email software. Any help will be much appreciated. -- Patty Ayers | www.WebDevBiz.com Free Articles on the Business of Web Development Web Design Contract, Estimate Request Form...

Formating, paragraph marks, boxes etc, how do I get rid of them?
One day I opened up excel and it had all these marks for formating in my form. This also happened in word. How do I get rid of them? Josh: In Word, paragraph marks can be turned on/off by clicking the paragraph icon (looks like a capital P but with two, vertical strokes instead of one). I just finished wandering through Excel 2000 looking for the same icon and the only mention of the paragraph mark is related to object linking and embedding. I've also done a lot of forms in Excel and don't remember ever running into paragraph marks. Are you using OLE in the spreadsheet...

Hold a cell range for an "average formula"
I add a column every week to my work sheet and want the formula to always calculate the current 5 column range. Wendy Akers wrote: > I add a column every week to my work sheet and want the formula to always > calculate the current 5 column range. > > Add a column WHERE? What "formula"? Where is the "current 5 column range"? Hi, Let's say your data is in range C3:H3. In cell K3, enter the following array formula (Ctrl+Shift+Enter) to get the average =AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,...

Conditional Formatting
I am using the below to auto change color if it meets the condition... =IF(F26>=0,IF(OR((F26)<=2,(F26)>=-1),"GREEN", IF(AND((F26)<=2.5,(F26)<=-1.5),"AMBER","RED")), IF(AND((F26)>=2,(F26)<=-1),"GREEN", IF(AND((F26)>=2.5,(F26)>=-1.5),"AMBER","RED"))) Any help is appreciated. -- J~ In 2003, when you set up a conditional format, you can choose one color for the condition. If you want to do something like what you are showing, you're going to need to use a Worksheet_Change event and code something ...

conditional format of data tables in charts
I've created a chart that is based on a data set where the cells have conditional formatting (if value a > value b then colour the cell red) I have the data table displayed with the chart (in chart options, 'show data table' is ticked), but the condtional formatting is not displayed in this table, i.e cells that are red in my original data set are not coloured in the chart table. Is there any way to apply the conditional format on the table displayed within the chart? Cheers Dan. Daniel, Unfortunately you can't apply conditional formating to data tables. However, yo...

Formatting the Date in Excel
Is there any way in Excel that I can format the Date so I don't have to type any slashes between the month and day? In other words, I would like to be able to type in 0714 press "enter" and have excel recognize that this is 07/14/2005 without typing in 07/14. Any suggestions would be appreciated. Thanks, Short of writing code, you could enter your numerics only date in cell A1, for instance: 07142005. Then in B1 write a formula: =mid(A1,1,2)&"/"&mid(A1,3,2)&"/"&left(A1,4) Then you could copy column B and paste as values into their req...