2 Conditional formats and insert comment - ajit

1.  I want to format the cell by 2 conditions. e.g. in range a1:z1, fill the 
cell with
red colour(Fill colour) only if it is blank and if it contains "Sunday", 
show it with red colour (Font Colour). HOw can I do this?
2.  I want to insert the comment conditionally e.g. if the cell contains 
"IT" insert the comment "India Today", "TOI" insert comment as "Times of 
India" etc. How Can I 
perform this operation?

Ajit
-- 
Knowldege is Power
0
ajitmunj (42)
6/2/2005 9:33:01 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
617 Views

Similar Articles

[PageSpeed] 53

> 1.  I want to format the cell by 2 conditions. e.g. in range a1:z1, fill
the
> cell with
> red colour(Fill colour) only if it is blank and if it contains "Sunday",
> show it with red colour (Font Colour). HOw can I do this?

Select the entire range A1:Z1. Go to Format > Conditional Formatting. select
for cell value is "equal to" and enter "" (which means blank). Click on
Format button, Paterns tab, and select red color. Click Ok.

Now, click on Add, and repeat the above process. This time instead of "",
enter "Sunday", format button, font tab, and select red. Ok.


> 2.  I want to insert the comment conditionally e.g. if the cell contains
> "IT" insert the comment "India Today", "TOI" insert comment as "Times of
> India" etc. How Can I
> perform this operation?

for this, you would require a macro.

Mangesh



0
6/2/2005 10:05:06 AM
How can it be blank and be Sunday?

To test for Sunday, use a formula of

=WEEKDAY(A1)=1

-- 
 HTH

Bob Phillips

"Ajit Munj" <ajitmunj@microsoft.com> wrote in message
news:6DD2B83B-60A7-435A-945E-334FB15B34B9@microsoft.com...
> 1.  I want to format the cell by 2 conditions. e.g. in range a1:z1, fill
the
> cell with
> red colour(Fill colour) only if it is blank and if it contains "Sunday",
> show it with red colour (Font Colour). HOw can I do this?
> 2.  I want to insert the comment conditionally e.g. if the cell contains
> "IT" insert the comment "India Today", "TOI" insert comment as "Times of
> India" etc. How Can I
> perform this operation?
>
> Ajit
> -- 
> Knowldege is Power


0
phillips1 (803)
6/2/2005 10:21:59 AM
I have the same problem.  I want blank cells to be flagged with as red.  
However, I did as you suggested and it did not work.  I have verified the 
cell is truly empty.  Is there some special trick to keying the double 
quotes?  I've tried " ", "", ="", 4 single quotes, all to no avail. 

"Mangesh Yadav" wrote:

> 
> > 1.  I want to format the cell by 2 conditions. e.g. in range a1:z1, fill
> the
> > cell with
> > red colour(Fill colour) only if it is blank and if it contains "Sunday",
> > show it with red colour (Font Colour). HOw can I do this?
> 
> Select the entire range A1:Z1. Go to Format > Conditional Formatting. select
> for cell value is "equal to" and enter "" (which means blank). Click on
> Format button, Paterns tab, and select red color. Click Ok.
> 
> Now, click on Add, and repeat the above process. This time instead of "",
> enter "Sunday", format button, font tab, and select red. Ok.
> 
> 
> > 2.  I want to insert the comment conditionally e.g. if the cell contains
> > "IT" insert the comment "India Today", "TOI" insert comment as "Times of
> > India" etc. How Can I
> > perform this operation?
> 
> for this, you would require a macro.
> 
> Mangesh
> 
> 
> 
> 
0
KarenF (4)
6/2/2005 8:37:20 PM
Hi KarenF

I have tested the solution and it works fine. But you have to take care and
go back and see the conditions again especially when you use text (strings)
where you need to enter double quotes ("). When you enter your condition the
first time, say for testing a blank, you would enter "". This changes to
"""""" or something like this. You need to go back and change it to "".
Similarly for "Sunday", just check if it displays this with 2 " only.

So just go back to the conditional format and check if your blank is ="", if
not, make it that way.

Mangesh



"KarenF" <KarenF@discussions.microsoft.com> wrote in message
news:799CD22B-7BA2-4437-B032-CCC722958C19@microsoft.com...
> I have the same problem.  I want blank cells to be flagged with as red.
> However, I did as you suggested and it did not work.  I have verified the
> cell is truly empty.  Is there some special trick to keying the double
> quotes?  I've tried " ", "", ="", 4 single quotes, all to no avail.
>
> "Mangesh Yadav" wrote:
>
> >
> > > 1.  I want to format the cell by 2 conditions. e.g. in range a1:z1,
fill
> > the
> > > cell with
> > > red colour(Fill colour) only if it is blank and if it contains
"Sunday",
> > > show it with red colour (Font Colour). HOw can I do this?
> >
> > Select the entire range A1:Z1. Go to Format > Conditional Formatting.
select
> > for cell value is "equal to" and enter "" (which means blank). Click on
> > Format button, Paterns tab, and select red color. Click Ok.
> >
> > Now, click on Add, and repeat the above process. This time instead of
"",
> > enter "Sunday", format button, font tab, and select red. Ok.
> >
> >
> > > 2.  I want to insert the comment conditionally e.g. if the cell
contains
> > > "IT" insert the comment "India Today", "TOI" insert comment as "Times
of
> > > India" etc. How Can I
> > > perform this operation?
> >
> > for this, you would require a macro.
> >
> > Mangesh
> >
> >
> >
> >


0
6/3/2005 4:36:06 AM
That did the trick!  Thank you Mangesh...

"Mangesh Yadav" wrote:

> Hi KarenF
> 
> I have tested the solution and it works fine. But you have to take care and
> go back and see the conditions again especially when you use text (strings)
> where you need to enter double quotes ("). When you enter your condition the
> first time, say for testing a blank, you would enter "". This changes to
> """""" or something like this. You need to go back and change it to "".
> Similarly for "Sunday", just check if it displays this with 2 " only.
> 
> So just go back to the conditional format and check if your blank is ="", if
> not, make it that way.
> 
> Mangesh
> 
> 
> 
> "KarenF" <KarenF@discussions.microsoft.com> wrote in message
> news:799CD22B-7BA2-4437-B032-CCC722958C19@microsoft.com...
> > I have the same problem.  I want blank cells to be flagged with as red.
> > However, I did as you suggested and it did not work.  I have verified the
> > cell is truly empty.  Is there some special trick to keying the double
> > quotes?  I've tried " ", "", ="", 4 single quotes, all to no avail.
> >
> > "Mangesh Yadav" wrote:
> >
> > >
> > > > 1.  I want to format the cell by 2 conditions. e.g. in range a1:z1,
> fill
> > > the
> > > > cell with
> > > > red colour(Fill colour) only if it is blank and if it contains
> "Sunday",
> > > > show it with red colour (Font Colour). HOw can I do this?
> > >
> > > Select the entire range A1:Z1. Go to Format > Conditional Formatting.
> select
> > > for cell value is "equal to" and enter "" (which means blank). Click on
> > > Format button, Paterns tab, and select red color. Click Ok.
> > >
> > > Now, click on Add, and repeat the above process. This time instead of
> "",
> > > enter "Sunday", format button, font tab, and select red. Ok.
> > >
> > >
> > > > 2.  I want to insert the comment conditionally e.g. if the cell
> contains
> > > > "IT" insert the comment "India Today", "TOI" insert comment as "Times
> of
> > > > India" etc. How Can I
> > > > perform this operation?
> > >
> > > for this, you would require a macro.
> > >
> > > Mangesh
> > >
> > >
> > >
> > >
> 
> 
> 
0
KarenF (4)
6/3/2005 2:42:03 PM
Hi KarenF,

Thanks for the feedback.

Mangesh



"KarenF" <KarenF@discussions.microsoft.com> wrote in message
news:CAAB7654-1855-4991-A060-1992BCF47936@microsoft.com...
> That did the trick!  Thank you Mangesh...
>
> "Mangesh Yadav" wrote:
>
> > Hi KarenF
> >
> > I have tested the solution and it works fine. But you have to take care
and
> > go back and see the conditions again especially when you use text
(strings)
> > where you need to enter double quotes ("). When you enter your condition
the
> > first time, say for testing a blank, you would enter "". This changes to
> > """""" or something like this. You need to go back and change it to "".
> > Similarly for "Sunday", just check if it displays this with 2 " only.
> >
> > So just go back to the conditional format and check if your blank is
="", if
> > not, make it that way.
> >
> > Mangesh
> >
> >
> >
> > "KarenF" <KarenF@discussions.microsoft.com> wrote in message
> > news:799CD22B-7BA2-4437-B032-CCC722958C19@microsoft.com...
> > > I have the same problem.  I want blank cells to be flagged with as
red.
> > > However, I did as you suggested and it did not work.  I have verified
the
> > > cell is truly empty.  Is there some special trick to keying the double
> > > quotes?  I've tried " ", "", ="", 4 single quotes, all to no avail.
> > >
> > > "Mangesh Yadav" wrote:
> > >
> > > >
> > > > > 1.  I want to format the cell by 2 conditions. e.g. in range
a1:z1,
> > fill
> > > > the
> > > > > cell with
> > > > > red colour(Fill colour) only if it is blank and if it contains
> > "Sunday",
> > > > > show it with red colour (Font Colour). HOw can I do this?
> > > >
> > > > Select the entire range A1:Z1. Go to Format > Conditional
Formatting.
> > select
> > > > for cell value is "equal to" and enter "" (which means blank). Click
on
> > > > Format button, Paterns tab, and select red color. Click Ok.
> > > >
> > > > Now, click on Add, and repeat the above process. This time instead
of
> > "",
> > > > enter "Sunday", format button, font tab, and select red. Ok.
> > > >
> > > >
> > > > > 2.  I want to insert the comment conditionally e.g. if the cell
> > contains
> > > > > "IT" insert the comment "India Today", "TOI" insert comment as
"Times
> > of
> > > > > India" etc. How Can I
> > > > > perform this operation?
> > > >
> > > > for this, you would require a macro.
> > > >
> > > > Mangesh
> > > >
> > > >
> > > >
> > > >
> >
> >
> >


0
6/4/2005 4:33:55 AM
If you use formula is instead you won't have that problem

-- 
Regards,

Peo Sjoblom

(No private emails please)



-- 
Regards,

Peo Sjoblom

(No private emails please)


"KarenF" <KarenF@discussions.microsoft.com> wrote in message 
news:CAAB7654-1855-4991-A060-1992BCF47936@microsoft.com...
> That did the trick!  Thank you Mangesh...
>
> "Mangesh Yadav" wrote:
>
>> Hi KarenF
>>
>> I have tested the solution and it works fine. But you have to take care 
>> and
>> go back and see the conditions again especially when you use text 
>> (strings)
>> where you need to enter double quotes ("). When you enter your condition 
>> the
>> first time, say for testing a blank, you would enter "". This changes to
>> """""" or something like this. You need to go back and change it to "".
>> Similarly for "Sunday", just check if it displays this with 2 " only.
>>
>> So just go back to the conditional format and check if your blank is ="", 
>> if
>> not, make it that way.
>>
>> Mangesh
>>
>>
>>
>> "KarenF" <KarenF@discussions.microsoft.com> wrote in message
>> news:799CD22B-7BA2-4437-B032-CCC722958C19@microsoft.com...
>> > I have the same problem.  I want blank cells to be flagged with as red.
>> > However, I did as you suggested and it did not work.  I have verified 
>> > the
>> > cell is truly empty.  Is there some special trick to keying the double
>> > quotes?  I've tried " ", "", ="", 4 single quotes, all to no avail.
>> >
>> > "Mangesh Yadav" wrote:
>> >
>> > >
>> > > > 1.  I want to format the cell by 2 conditions. e.g. in range a1:z1,
>> fill
>> > > the
>> > > > cell with
>> > > > red colour(Fill colour) only if it is blank and if it contains
>> "Sunday",
>> > > > show it with red colour (Font Colour). HOw can I do this?
>> > >
>> > > Select the entire range A1:Z1. Go to Format > Conditional Formatting.
>> select
>> > > for cell value is "equal to" and enter "" (which means blank). Click 
>> > > on
>> > > Format button, Paterns tab, and select red color. Click Ok.
>> > >
>> > > Now, click on Add, and repeat the above process. This time instead of
>> "",
>> > > enter "Sunday", format button, font tab, and select red. Ok.
>> > >
>> > >
>> > > > 2.  I want to insert the comment conditionally e.g. if the cell
>> contains
>> > > > "IT" insert the comment "India Today", "TOI" insert comment as 
>> > > > "Times
>> of
>> > > > India" etc. How Can I
>> > > > perform this operation?
>> > >
>> > > for this, you would require a macro.
>> > >
>> > > Mangesh
>> > >
>> > >
>> > >
>> > >
>>
>>
>> 

0
terre081 (3244)
6/4/2005 4:39:23 AM
Reply:

Similar Artilces:

IF FORMULAS #2
Looking for help...... In column A1:A16, I have names. In B1:B16 I will assign random numbers to those names from 1-16. In another part of the sheet I have 16 columns lets say D:S, (D) being column 1 and (S) being column 16. when I assign a number in column B, I want the name to the left to appear above the appropiate column. EX, If B1 is 16 then the name in A1 will appear above column S. I am using Excel 2007 Windows Vista. I find this site to be the place for answers,everyone is great. Thanks in advance. D1: =INDEX($A$1:$A$16,MATCH(COLUMN()-3,$B$1:$B$16,0)) Fill to S1. "JOHN...

Shared Workbooks #2
I have 1 book being shared by 2 users. The book has approx 30 worksheets. The sheets have a min of 10 rows to a max of 11,000 rows and 26 columns of data. User 1 and user 2 open the workbook and work on different sheets of the book at the same time. User 1 changes the format of cells, saves the changes and exits Excel. User 2 has the file open, continues to work on the worksheet, user 1 reopens the book, makes changes, saves, and exits. When user 2 saves and exits the book and user 1 reopens the book, the formatting is lost. The file is also 24+mb, does this have a factor...

Missing row #2
A user has deleted a row that had an amount in it. Her rows are now number, 23, 24, 25, 27 (so row 26 is missing). Whenever she tries to total columns or rows, the number that is in that missing row is calculated in. How do I retrieve that row, or clear out the number? TIA. Kim Select rows 24 and 26, the whole rows and goto Format>Row>Unhide. -- HTH RP (remove nothere from the email address if mailing direct) "Kim" <Kim@discussions.microsoft.com> wrote in message news:37603B87-33DA-4EB0-AC54-68F919F4A8B8@microsoft.com... > A user has deleted a row that ha...

language date formatting
My PC is regional setting is dutch I have to print documents with date format dd-mmm-yyyy Problem is, that mmm should always be English (not dutch). How to enforce that? The only way I can think of is to write a custom VBA function to return the month abbreviation. UNTESTED sample function with no error handling follows Public Function fEnglishDate (DateIn as Variant) Dim dateString as String If IsDate(DateIn) = False Then fEnglishDate = DateIn Else Select Case Month(DateIn) Case 1 : "Jan" Case 2: "Feb" ... ...

CRM1.2 and Contract Template
Is there any way to customize the "Contract Template" screen from the "home","settings","template manager","Contract Templates","New Contract Template".. Can I customize this form ? I need to enter "Reponce Levels" in the dropdown list... Is this possible and if so how....? Hi Ulrich, Contract Template itself is not customizable. However, if you want the "Response Levels" picklist to show up on each contract that gets created, you could customize the Contracts Form itself and add the picklist. This field...

spin control problem #2
How do I get the integer value of a spin control's associated edit control while in the edit control's OnEnChange function. Everytime I try to extract the data I get an exception error. If you have a problem in spin control, hire a good publicist... (sorry, I couldn't resist) First, show the code. Then say PRECISELY what you mean by "exception error", showing the exact line of code that experiences the problem. Otherwise, there is no way to tell what you've done! joe On Thu, 26 Jul 2007 07:43:21 -0700, "Z.K." <nospam@nospam.net> wrote: ...

Free business opportunity #2
If you need to make 2,000 - 5,000 per week - you need to check this out. No cost or obligation full details. www.profitsdaily.biz --- MAF Anti-Spam ID: 20041209205203P0d4HhB4 ...

Import of Data into RMS 2.0?
We will be converting to RMS soon. Is there anyway we can import our old sales history(Monthly) into RMS 2.0? Hi newbie. I am not aware of a free utility that will import all data, including sales transaction history to your RMS system. Retail Realm has an Import Tool that will do this if your current system is Microsoft Point of Sale, possibly POS 2009, but it is licensed (meaning you need to pay for it). Otherwise, I believe you are limited to importing items, customers, suppliers, and UPCs as Aliases, no history. HTH..... "newbierms user" wrote: >...

cannot see inserted pictures in publisher 2003
cannot see pictures in 2003, although frame adjusting dots appear. I open new page, select insert picture (doesn't matter where from, still no different). Help please. Program worked on original pc which originally had an earlier version of publisher installed. The program is not an update, but full version. PC P4 running XP home and office XP View, pictures, detailed display.. If this is not the solution, you may need an upgraded video driver. Do this: Slide the acceleration down on your adapter, control panel, display folder, settings tab, advanced button, troubleshoot tab. If thi...

Syntax error in INSERT INTO
Could someone please point out my error here? I'd greatly appreciate it! ssql = "INSERT INTO Tbl_UniqueCourts (Court1, Court2, Court3, League, Date, Rotation) VALUES ('" & sCourt1 & "','" & sCourt2 & "','" & sCourt3 & "'," & Forms!frmRotation!League & ",#" & dtDate & "#," & intR & ")" Is Leage a number? If not put a quote around that as well. Also, you may need to add a space after each comma in the VALUES portion. Or, it could be th...

Copying formatting of entire sheet
I made an Excel 2007 workbook which includes my monthly business income, parts bought, a logbook for car mileage dividing into personal and work and finally a financial end of year total constantly updated by the income or parts bought over the financial year. What I would like to know, please, is how to copy the entire logbook (12 sheets, 1 per month then 1 for end of financial year totals and 1 for car miles) FORMATTING so that I can have it all ready for the next financial year. I live in Australia and our financial year ends Jun 30 and starts the new one July 1. What I would like t...

Macros on Protected Sheets #2
Hi , Now I have tried Alan's Solution to be able to run my Macros in Protected sheets but as I am not VB literate, I think I have made a Syntax mistake. Sub Newaction() Sheet("Critical Path").Unprotect ("My Password") ' Newaction Macro ' Macro recorded 17/11/2004 by Clifford ' ' Selection.AutoFilter Field:=1, Criteria1:="=" Sheet("Critical Path").Protect ("My Password") End Sub I receive a message "Compile error" "Sub or function not defined" Can you tell me what's wrong ? Regards ...

After a hard drive format
Before I format my hard drive (it's time)what files do I need to backup to retain my emails and email addresses? Where do I find them? Thanks, Marvin If you are using Outlook 2000/2002, search for files with the extension ".pst". The exact location will depend on what operation system you are using. Regards Darren PS within your newsgroups settings, change your e-mail address to something like marskyNOSPAM@mindspring.com. Newsgroups are where spammers gain their e-mail addresses from by using "spiders" or "web-bots" to gain details of such addresses. ...

Excel Cell Formatting #3
How do I centeer a picture in an Excel cell? Hi you can't as such objects 'float' above the cells in Excel -- Regards Frank Kabel Frankfurt, Germany "Tom Coffey" <Tom Coffey@discussions.microsoft.com> schrieb im Newsbeitrag news:8E7356E1-4318-42F3-B9C6-3D7EDFBBEEA1@microsoft.com... > How do I centeer a picture in an Excel cell? ...

eseutil /p #2
I had to run eseutil /p since /r was not working, and my exchange 2003 priv and pub were not coming up. i ran it on pub1.ebd ran ok and i was able to mont the public folder. eseutil on priv is currently running and is at a point were it seems that nothing is happening, is says deleting unicode fixup table .? how long will this take. is it normal ..?? Thanks. Do you have a backup that you can restore from (if necessary)? How big is your priv? How long has eseutil been running? Might be time to get PSS on the phone. -- Ben Winzenz Exchange MVP "Alfred" <Alfred@...

Displaying Comments
I have trouble displaying any comments that are entered into either Word or PowerPoint docs by a Windows Office 2003 user. I am running MAC OS 10.3.7 and have tried to fix this in both Office X and the Office 2004 Demo without success. I have "View Comments" checked and if I enter a comment, I can see it. Thank you. Russ ...

mail merge- 2 things
Help! I have folks on my mailing list, same address but different last names. When I merge that way, it shows them correct but the others either.....sam smith and judy smith or The Smith family. In a perfict world I would like it to merge...... jen doe & sarah miller and sam & judy smith. What can I do? Next, when I save the mail merge to a cd to take to Kinkos to print they say "you need to load up this file prep tool & save it that way first, oh but you have XP so this won't work, you need to buy a distiller". Well I just looked at what I think is ...

Inserting new rows, which contain the formula in the above the new
I am not very technical when it comes to excel, and am creating a sheet. I have various information in each column, and in two of them I have formula eg - (on row 1) A1, B1 has personal info . C1, D1 have formula inserted, which work I have copied the formula/ data down to say Row 18. when I insert a new row inbetween rows 1 & 18 the formula is not copied down/ up eg - (on row 2) A2, B2 I want to remain blank, so I can input the data C2, D2 I want the formula automatically copied. How do I do this? Please help, and please try and keep it simple I'm not very compu...

To display number in text format in cells
I have a sequence of digits like 5026555306553 I entered in a cell and when the cursor left the cell it displays as 5.02656E+12 How do I set the cell or the whole spreadsheet to display as text rather than this format? Change the cell format to either: Number Accounting or Currency. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Man T" <alanpltse_NOSPAM@yahoo.com.au> wrote in messag...

Outlook 2007 Error #2
The Operation Failed. An Object Could Not Be Found. I get this error when I use Outlook 2007. I followed the instructions I saw on a Microsoft Support document that told me to create a new profile. I created a new profile, (no big deal), but it has not solved the problem. Any other ideas?? As far as creating a new profile, I deleted the original default profile I had and created a new one. anything more complicated than that? I have 3 different profiles: One for my GMail account One for my Verizon Account One for my one domain name account, (my default, or mail account). Did you use...

Trial version activation #2
When the dialog for activation over the inet pops up, it cannot find my connection. I use broadband cable and I know I am connected. Does anyone know how to correct this so that I can activate the software? Tia -- Jay ...

Unable to access OWA #2
i have no problem accessing OWA using the FQDN. eg. http://mail.company.edu.my/exchange But when tried to access OWA using public IP address, eg. http://203.115.220.20/exchange , it shows "The page cannot be found". DNS server has records for both forward & reverse. You should ask this in an Exchange group as Outlook Web Access is a part of Exchange, not Outlook. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading...

Macro to save file to a specific directory depending on the day of week #2
Many thanks to icestationzbra & AlfD it worked a treat However, can anyone please advise me how to incorporate both the time and dat into the formula " & trim(format(date,"dd-mmm") & ".xls") Many thanks Rob : -- robertgu 44 Married two kid ----------------------------------------------------------------------- robertguy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=596 View this thread: http://www.excelforum.com/showthread.php?threadid=26192 One way: MsgBox Format(Now, "dd-mmm_hhmmss") & ".xls&...

formatted numbers displayed as #####
Another office phoned me about a problem they are having with excel. They had a large list of dates about 4600 columns long that were al displayed as one long string of numbers, i.e. like 021105. So the went to format them into dates and they all ended up going into #####. As I was looking through the help topics on Excel I found that number will be displayed like this if the column is not big enough in width. So I got them to do that but to no avail, they are still displayed a #####. Also while doing my own testing I found that even if they d get the numbers shown they will probably get no...

2 axis bar graph
I have a 2 data item column graph for 6 data points in time. One in %, one is a number. I want to use a 2 axis graph, but when I try and change the axis on one of the data items the graph switches to a stack bar graph with 2 axis. Any thoughts on how to keep it a double column graph with 2 axis? It does not really change to a stacked bar chart. To illustrate, once you have set your axis, right click on one of them, then click on format data series. Now click on options, and change the gap width to say 40. You will see that your percentages line up with the relevant axis, while the...