Inserting comment depending on the Conditional formatting

Hi

Is it possible to insert comment depending on the conditiona
formatting.I mean if the color of the cell is blue the comment shown i
" A " & if the color of the cell is green the comment shown is  " B "
similarly if the color of the cell is black the comment shown is " C 
. ( The color names which i had writted here r examples i dont know th
real names of the colors which i have given in my spreadsheet)

--
Little Maste

-----------------------------------------------------------------------
Little Master's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2822
View this thread: http://www.excelforum.com/showthread.php?threadid=48097

0
11/2/2005 7:59:41 AM
excel 39879 articles. 2 followers. Follow

8 Replies
386 Views

Similar Articles

[PageSpeed] 1

Good morning Little Master

You can do this but you will need to use VBA (macros) to do so.  Thi
code will add the comment "tester" to the currently active cell if th
colour is dark green.  You haven't mentioned how proficient withi
Excel you are, so I have assumed you will know what to do with thi
code.  If you need any more help in implementing this then post back.

Sub Macro1()
On Error Resume Next
If ActiveCell.Interior.ColorIndex = 50 Then
ActiveCell.AddComment
ActiveCell.Comment.Text Text:="tester"
End If
End Sub

HTH

Dominic

--
dominic
-----------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1893
View this thread: http://www.excelforum.com/showthread.php?threadid=48097

0
11/2/2005 10:06:33 AM
Thanks Dominicb, thanks alot for your reply.But unfortunately i don
have any programming knowledge.Pls tell me wat to do with this code 

--
Little Maste

-----------------------------------------------------------------------
Little Master's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2822
View this thread: http://www.excelforum.com/showthread.php?threadid=48097

0
11/2/2005 11:38:08 AM
Hi Little Master

From Excel make sure that only the worksheet you want to apply this
code to is open (say mywork.xls).  Press alt + F11 and from the project
box make sure mywork.xls is selected.  Go to Insert > Module and a blank
window should open taking up most of the visible area.  Copy the code
into this area and then close the window.  To run the macro go to Tools
> Macro > Macros, select Macro1 from the list and run (or click on the
options button to set up a shortcut to run the routine).

Just remember that as it stands at the moment the code only checks for
ColorIndex 50 (which is dark green), and if you want to check for other
colours you will need to know the colour index.  On my system (XL2000) I
can press alt + F11 to open the macro editor, press F1 for help and
under the help index tab type colorindex, and a help page comes up that
shows all colours in the DEFAULT colour palette and their associated
numbers.  Hope that gets you started for now...

HTH

DominicB


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

0
11/2/2005 12:44:28 PM
Sorry dominicb , but i cant get it.


-- 
Little Master


------------------------------------------------------------------------
Little Master's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28227
View this thread: http://www.excelforum.com/showthread.php?threadid=480978

0
11/3/2005 7:30:15 AM
Hi Little Master

If you want to send me a private message with your e-mail address I'll
send you an example file and try and help you from there.

DominicB


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

0
11/3/2005 9:11:05 AM
hI!

i'm new in macros too and i'm so happy to have tried my 1st MACRO using
the guide u've given!

i wonder if there is any good web tutorial on macro u can recommend for
newbies like me?

thanx so much!


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

0
11/3/2005 9:30:16 AM
Hi Hmmm

Welcome to the club!

These are some good websites that I have learned lots from - plenty of
good examples - in no particular order.  But there are tons of other
sites out there, these are a couple of the good ones that I've found.

http://www.cpearson.com/excel/topic.htm
http://j-walk.com/ss/
http://www.contextures.com

Good luck!

HTH

DominicB


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

0
11/3/2005 9:56:44 AM
thanx!  ;)


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

0
11/3/2005 10:18:54 AM
Reply:

Similar Artilces:

Change default font format in Excel
I have an existing workbook with cell text entries in regular black font. I want to make a number of additional entries in various cells in this workbook, and I want all my text entries to be a different font format (bold, red). Is there a way to do this automatically without highlighting each entry I make and manually changing the cell format? Thanks. Michael, Copy the code below, right-click on your sheet tab, select "View Code" and paste the code in the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Cou...

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting" checked under "PivotTable Options", and "Autoformat Table" unchecked. Even so, I either lose portions of my formatting, or different formatting is applied when I refresh. Does anyone know what I am doing incorrectly or have any other suggestions? Thanks, Phil Other things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. --Instead of selecting the cells to format the numbers, right-click the field but...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to the left without automatic moving the odd numbers to the left as well? Can someone help me? Cissy99 wrote: > I've got a problem with my publication. I can't align even page numbers to > the left without automatic moving the odd numbers to the left as well? Can > someone help me? You need to create a two-page master rather than a one-page master. What version of Publisher are you running? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

how to turn off automatic format in Excel?
Hi Excel automatically change the first character in a cell to be uppercase. I just want lowercase. How can I turn off this function? Thanks Ngoc Hi Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence. Andy. "ngoc" <linh@chello.no> wrote in message news:BOKNb.271$O41.819@amstwist00... > Hi > Excel automatically change the first character in a cell to be > uppercase. I just want lowercase. How can I turn off this function? > Thanks > Ngoc > ...

Formatting
I've always used MS Word as my email editor in Outlook. Is this possible in Live Mail. Nope. I'm afraid that's only possible using Outlook. In WLM you have to use the built in editor. Is there anything in particular that's lacking from the built in editor that you're looking for? Colin Brown WL MVP "jrchambe" <jrchambe@discussions.microsoft.com> wrote in message news:5EC21892-D39F-4219-AB0F-47BC14E1CD36@microsoft.com... > I've always used MS Word as my email editor in Outlook. Is this possible > in > Live Mail. "...

cannot insert hyperlink
Hi - others had reported this to me before, and at that time, it worked perfectly well for me - but now, my system has the same problem: I cannot insert a hyperlink into an email message. It's in HTML format, so I select a word of text, and use Insert / Hyperlink, enter the URL - but after that box closes, there's still no hyperlink. I am aware of the fact that hyperlinks will only be clickable after the message will have been sent; however, now, they aren't any more. And, before, I'd at least see the text formatted like a link - that's no longer the case. Any hints ? ...

Formating
Hi, In a Column 'A' sales Commission is calculated and resulta is as 250, 200, 300, 330. I wanted to format a cell with a Blinking colours where value is 100 to 250, 250 to 300, 300 to 350 and 350 & above. please help to format my sheet ... thanks Ismail, Getting your cells to "blink" is not in the standard Excel formats What you want would require some extensive programming, certainly, ou of my capabilities. However, there are some great things you can do with Excel' CONDITIONAL FORMATTING. Let's say in column A you have numbers that are the result of formu...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

Background changes conditionally
Hi all, I have a spreadsheet that shows the floor plan layout of my call centre. On each desk, the desk number and identity are displayed. I would like to colour the background for those pcs that are on the same subnet. I have a sheet called data - it contains Table Number, Desk Number, PC ID, IP Address. How could I get it to change the background colour if the pc is on subnet 162, and set a different colour for those on subnet 167 any ideas? -- PeterG ------------------------------------------------------------------------ PeterG's Profile: http://www.excelforum.com/member.p...

Opening and formating a CSV file?
Could someone remind me how to handle CSV files please? I had assumed the Text Import Wizard would pop-up when I use File|Open, but the data goes straight into the worksheet. (Presumably because it is not 'delimited'?) Without the Wizard at my side, how do I get each comma-separated field in its own column? -- Terry, West Sussex, UK Try renaming your .csv file to .txt Terry Pinnell wrote: > > Could someone remind me how to handle CSV files please? I had assumed > the Text Import Wizard would pop-up when I use File|Open, but the data > goes straight into the worksheet...

Problem access variable in On Format
Hi, I'm using Access via Office XP Pro. I am trying to format the Zip Code on the detail line of my report. I have tried the following lines of code in both the On Format event and the On Print event: If len([PostalCode]) > 5 Then : : end if or if len(Me.PostalCode) > 5 Then : : end if In both events and either code, I receive the error message: Access can't find the field 'PostalCode' referred to in your expression. If I put "PostalCode" as the source of the report's control...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Problem inserting delta symbol into Excel
Dear Group, Hello. I recently posted a question related to the issue of putting a mu (=B5) symbol into MS Excel charts. Someone kindly suggested using the Alt-numeric code (alt-0181) to place this symbol into the spreadsheet cell which then showed up nicely in the chart. However, when I try to do this same operation using alt+ NumPad 68 (susposed to give the delta symbol), all I get is a capital D. What gives? Why does one symbol work and not the other? I even downloaded the program QuickKeys from SourceForge.net and this still did not solve the problem. Any ideas?? Thank you....

format a CD
Hi, how can I format a cd+rw re writeable disc aga ...

Insert new row as cell contents change
Insert new row as cell contents change. After importing data I have a spread sheet with a column that contains a series of alpha numeric characters. At various random intervals in this column the contents change. EG rows 1 to 4 could contain ABC, then rows 5 to 15 could become 222. I am looking for a method to insert a blank row automatically between the rows were the contents change. Many Thanks Geo George If you are familiar with VBA the code below will do what you want. Preselect the column of data first Sub InsertRowAfterValueChange() Dim myCell As Range Dim sCurrVal As String ...

SUMIF with two conditions ?
:cool: OK I have tried just about everything and can not find the righ combination for seems like a simple problem: IN A1 IF N79:N83 >=1 AND IF N79:N83 <=7 THEN SUM K79:N8 -- Calvi ----------------------------------------------------------------------- Calvin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=590 View this thread: http://www.excelforum.com/showthread.php?threadid=26341 Hi Calvin one way =SUMPRODUCT(--(N79:N83>=1),--(N79:N83<=7),N79:N83) Cheers JulieD "Calvin" <Calvin.1d4dfz@excelforum-nospam.com> wrote in message...

Comments in Excel #2
When you create a comment the font and size defaults to "Tahoma", "8". Is there a way of changing the default font and size for comments so that when you create comments after changing the default they will all be the font and size that you set as the default. eg. "Arial", "10". You can change the default font size: Right-click on the desktop, and choose Properties On the Appearance tab, click Advanced. From the Item dropdown, choose Tooltip Choose a font Size, click OK, click OK or use a macro to insert a comment with specific f...

how to format and print labels from an excel file
I have an excel list of people with 12 cells of information. I want to format and print "name tags" (labels?) using only the information in four cells. How can I do this? You can print labels in Excel but formatting can be a problem. Best to use Word for the mailmerge operation and select the 4 labels to print during the setup in Word. For help on Word mail merge using Excel as the data source. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm And a t...

Remove format link?
Is there a way to remove the format link between fields in Modifier? Help just shows how to set it and the usual stuff doesn't seem to be working. ...

conditional formmating
In conditional formatting I would like a certain row of dates to turn a colour when a report is sue in 60 days and they a different colour when there due in 30 days. Also included in the formula I need the colour to remain blank if there are no dates in the column In A9 I have the date 1/Nov/2009 In B10 I have =A9+15 and this is copied across to L9 So I have a row of dates in 15 day steps I selected A9:L9 and used this formula in the Conditional Formatting dialog =A9>TODAY()+60 (colour red) Then I added =A9>TODAY()+30 (color blue) So I want to know what dates are 60 day...

Word doc format
I tried to email a word attachment written on office for mac 2008 home version, but the recipient couldn't open the doc. recipient using windows xp. rlciii@officeformac.com <rlciii@officeformac.com> wrote: > I tried to email a word attachment written on office for mac 2008 home > version, but the recipient couldn't open the doc. recipient using windows > xp. If they don't have a recent version of Windows Office, they may not be able to open a .docx file. Choose File > Save As and save as a .doc file. -- Adam Bailey | Chicago, Illinois adamb@lull.org | Finge...

Currency Format Lost in Union Query
Thanks to John Spencer, I resolved one problem in my union query (Thank you, John). But I have another problem. Some of my fields in my union query are currencies and others percents. I see both formats are lost. I am sure it is a union query problem, because when I ran an individual query, none of the formats were lost. Is there any way I can keep currency and percent formats without writing, FORMATCURRENCY, etc for each field? Thank you. What data types are these fields? The field in a UNION query will normally take on the data type of the field in the first SELECT. So, if ...

Cell Shading > 3 conditions
I have a spreadsheet that contains up to 6 different text strings. I'd like to automatically change the text color or the cell shading depending on the specific condition. For example, if the cell contains "A", make the cell shading red. If it contains "B", change the font color to red. Excel's conditional formatting limits me to 3 conditions. How can I accomplish this objective? I'm using Office 98 on a Mac, and worse, I'm not VBA smart. Thanks. rick -- rabsparks ------------------------------------------------------------------------ rabsparks's ...