VBA code to automatically colour cells depending on text?

Hi,  - with the skill of Don Guillet (Excel MVP - thank you!!) I have nearly 
finished a ‘to-do’ list to help organise my life as an infant school teacher. 
see attached link:

http://www.box.net/shared/ejzn141dux 
 
 The worksheet works like this:

Enter desired text in cell ‘c3, h3 or m3’ and then use the drop down button 
in ‘b3, g3 or l3’ to determine the position where the text is to be placed in 
the below list. If there is text already in that position, all the text below 
is moved down one cell. If you delete text from a cell then the text in the 
cells below moves up one. This part of it works fine.

 The VBA code currently being been used is; 
 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) 'SAS
Application.EnableEvents = False
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B3,G3,L3")) Is Nothing Then
    
    With Cells(Target + 5, Target.Column + 1)
        If Len(Application.Trim(.Value)) < 1 Then
            .Value = Target.Offset(, 1).Value
        Else
             Target.Offset(, 1).Copy
            .Insert Shift:=xlDown
        End If
    End With
End If
    
Range("C6:C105,H6:G105,M6:L105").SpecialCells(xlCellTypeBlanks).Delete 
Shift:=xlUp
Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
I would like to know how to adapt the above code so that if I type a phrase 
containing the word ‘Trago’ into cells c3, h3 or m3 the selected destination 
cell would be highlighted light red?

I have tried conditional formatting but it does not retain after more than 
one go – so I think the solution is adapting the above code?

Yours hopefully,
Matt.

0
Utf
1/2/2010 7:44:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
2126 Views

Similar Articles

[PageSpeed] 47

Also posted and being answered here 'VBA code to automatically colour
cells depende on text? - MrExcel Message Board'
(http://www.mrexcel.com/forum/showthread.php?t=438697)

mj_bowen;600649 Wrote: 
> Hi, - with the skill of Don Guillet (Excel MVP - thank you!!) I have
> nearly
> finished a ‘to-do’ list to help organise my life as an infant
> school teacher.
> see attached link:
> 
> 'Book1.xlsm - File Shared from Box.net - Free Online File Storage'
> (http://www.box.net/shared/ejzn141dux)
> 
> The worksheet works like this:
> 
> Enter desired text in cell ‘c3, h3 or m3’ and then use the drop
> down button
> in ‘b3, g3 or l3’ to determine the position where the text is to be
> placed in
> the below list. If there is text already in that position, all the text
> below
> is moved down one cell. If you delete text from a cell then the text in
> the
> cells below moves up one. This part of it works fine.
> 
> The VBA code currently being been used is;
> 
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range) 'SAS
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> If Not Intersect(Target, Range("B3,G3,L3")) Is Nothing Then
> 
> With Cells(Target + 5, Target.Column + 1)
> If Len(Application.Trim(.Value)) < 1 Then
> .Value = Target.Offset(, 1).Value
> Else
> Target.Offset(, 1).Copy
> .Insert Shift:=xlDown
> End If
> End With
> End If
> 
> Range("C6:C105,H6:G105,M6:L105").SpecialCells(xlCellTypeBlanks).Delete
> Shift:=xlUp
> Application.CutCopyMode = False
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> End Sub
> 
> I would like to know how to adapt the above code so that if I type a
> phrase
> containing the word ‘Trago’ into cells c3, h3 or m3 the selected
> destination
> cell would be highlighted light red?
> 
> I have tried conditional formatting but it does not retain after more
> than
> one go – so I think the solution is adapting the above code?
> 
> Yours hopefully,
> Matt.


-- 
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=166472

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
Simon
1/2/2010 9:48:40 PM
Reply:

Similar Artilces:

Error with VBA code
I am having error with this statement. Do anyone know what's the error with this line: ..CheckBoxes(i).Value = False Dim i As Integer For i = 1 To 19 ..CheckBoxes(i).Value = False Next i I would guess that you have fewer than 19 checkboxes. -- Jim <xiaodan86@hotmail.com> wrote in message news:1178702221.638576.248520@q75g2000hsh.googlegroups.com... |I am having error with this statement. Do anyone know what's the error | with this line: | .CheckBoxes(i).Value = False | | | Dim i As Integer | For i = 1 To 19 | .CheckBoxes(i).Value = False | Next i | ...

change control source or value of text box
I have a report with GroupHeader0 and GroupHeader1. The data source of the report is a query. In the GroupHeader0 I have the Purchase Order Header info which includes the Purchase Order due date. In the GroupHeader1 I have the Purchase Order line info which includes the line due date. What I need is if the line due date is Null or blank to default to the PO header due date. How can I assign the header due date to the line due date. Can I change the control source of the text box in VB programming. -- MNJoe Put the following expression in the first blank field in your query: ...

Display Field Name as Text Listing if Value True from Checkbox Fields
Hi everyone! I have 40 or so columns in a table using the yes/no data type. Each record in the table represents an audit of a paper form. Those items filled incorrectly or incompletely on the paper form receive a check on an access form during the audit process. I would like to create a feedback form based on the audit record that displays a listing of fields that received the check boxes, or rather, were incomplete or incorrect on paper form that was audited. On the feedback form, I only want to display something like this: "Your form had the following errors: ErrorFieldOneName Erro...

Preserving Cell Formats in Excel Query
I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't carry through to the query results. Is there a way to carry original formatting through to Excel Query results Any insight would be appreciated Karen S No, you can import the data, but not the formats. If you're importing programmatically, you could apply the formatting as part of the import procedure. Karen S wrote: > I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't car...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

paste-linking annotating text boxes
I'm trying to insert an Excel chart into a PowerPoint presentation and link it dynamically. The data link is not an issue but I cannot find a practical way to paste the text boxes that were added to the Excel chart by way of annotation. Selecting and pasting the chart itself and the textboxes as if they were separate objects results in a distorted PowerPoint layout. Is my only choice to add the text boxes anew in PowerPoint? Select the chart prior to adding the textbox. The textbox then will be part of the chart, rather than just another drawing object floating above the worksh...

Automatic Update application ignores permissions
I have a managed account on my computer - I've allowed the user to launch all Office applications except the Updater, Remove Office, and the Handheld Sync Installer. However, if the user clicks "Check for updates" in the Help menu in Word, the Updater application launches, and the AU daemon is put in the list of applications to startup on login. I guess this is the place to report such a bug - does anyone know how to prevent the user from launching the Updater application in this way? Does the user account have the "Allow Supporting Programs" box in the allow/disall...

How to goto cell containing specific date
Thought I asked this before, but can't find the thread w/ my question or any replies... I have a worksheet wih a full year's dates in the cells running down a colum, with other data for each date in the the adjacent columns; Instead of scrolling up & down to a cell with a specific date I'm looking for, is there another way to goto a cell containing a specific date? (e.g., today(), or another specific date) In case this is pertinent: the date series begins with the entry of one date (e.g., 01/01/2010 in cell A1), with the dates in subsequent rows arrived at ...

Different background color depending on x-axis value
Hi! Is there any way to have the background in a chart have differen colors depending on x-axis values? Like x-axis value 1-10 => blue background x-axis value 10-20 => red background Thank -- RealRave ----------------------------------------------------------------------- RealRaven's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3705 View this thread: http://www.excelforum.com/showthread.php?threadid=56778 Here are a few of the ways you can get custom background fills for your charts" http://PeltierTech.com/Excel/Charts/ChartIndex.html#Backgro...

VBA project password
I've locked one of my ms access vba project by password for sometime and now i forget abt it, are there any utilities can review maybe just 2 charaters or more ? if i can know that i can remember. Thanks Daniel "Daniel" <danieltbt04@yahoo.com> wrote > I've locked one of my ms access vba project by password for sometime and now > i forget abt it, are there any utilities can review maybe just 2 charaters > or more ? if i can know that i can remember. Thanks Did you Google on all the words "Access" "password" and "recovery&qu...

How do I turn the pivot chart into a list with all cells filled?
I have the pivot chart and would like to copy and paste it so that all fields are filled Select the pivot table. Choose Edit>Copy Select the cell where you'd like to paste the copy Choose Edit>Paste Special Select Values, click OK There are instructions here for filling the blanks: http://www.contextures.com/xlDataEntry02.html gianna wrote: > I have the pivot chart and would like to copy and paste it so that all fields > are filled -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Blurry text in Bold
Bold characters in excel cells are blurred (reduced clarity) regular text appears fine. Please advise on remedy I understand that Word 2003 has similar issues on regular font. (deactivate ClearType was solution for word) I cannot find any info regarding Excel 2003 in knowledge base ...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

How to setup Automatic Send/Receive time interval ?
I once knew where to click to set the time interval for auto send/ receive. Where do I click in Outlook 2003 to be able to set the automatic send/ receive time interval? Thanks for any help. Mel <MyEmailList@gmail.com> wrote: > I once knew where to click to set the time interval for auto send/ > receive. > > Where do I click in Outlook 2003 to be able to set the automatic send/ > receive time interval? While viewing a mail folder, press ALT-CTRL-S -- Brian Tillman On Jul 9, 2:57 pm, "Brian Tillman" <tillman1...@yahoo.com> wrote: > Mel <MyEmai...

Howdo I stop code execution with the Cancel button?
Hi I have a procedure that takes a while and the user might want to hi cancel. How can I make the Cancel button replicate the cntrl brea event and unload the form? Cheers Gromi -- Gromi ----------------------------------------------------------------------- Gromit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=92 View this thread: http://www.excelforum.com/showthread.php?threadid=31469 ...

Conditional Cell Fill?
Hello, Is there a way to use fill colors based on formulas? Thanks, Rusty Look at conditional formatting in help -- Regards, Peo Sjoblom "Rusty Williamson" <rusty@uno.sd.znet.com> wrote in message news:RUGlb.45764$Z86.33887@twister.socal.rr.com... > Hello, > > Is there a way to use fill colors based on formulas? > > Thanks, > Rusty > > ...

Import plain text with formulas into Excel
I'm having some trouble in Excel. If I create a plain text file like the following: 1,2,3 4,5,6 =sum(a1:a2), =sum(b1:b2) Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications. I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for... Any good sugestions? Regards -- schmiedel ---------------------------------...

Text fields in report writer
I have a modified report dictionary, when I go into a report layout and enter a text field, when I tab off the field what I wrote is converted to something totally different. Has anyone seen this before? Reports that I have been using for months suddenly the text fields print something totally different. ????? Any suggestions would be helpful. Thank you! Hi J. We use a shared dictionary located on our server, and I find text fields do not "travel" well - ie. I often lose info if I export a report out of the dictionary to work on it. What I do is make a copy of the enti...

Error Code 800ccc0f
Hi. I´ve gotten the error code above with protocol IMAP. The port is correct. And the account and server name are correct too. I can´t even move a message from one file to other. Can someone help me? Thanks. Renato Please post the complete error message. Error Code 800CCCOF can be many different things depending what the error say's -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. http://www.microsoft.com/protect "Renato-Brazil" <Renato-Brazil@discussions.micros...

Formatting hyperlinks in an Excel cell 02-16-10
Two of the columns in a spreadsheet (Excel 2003) that I use record email and web addresses. All of them appear as hyperlinks i.e. blue and underlined but some occasionally seem to lose their hyperlink properties. This means that when one hovers over them, the cursor stays as the usual Excel cross rather than changing to the hand/finger symbol. Also, clicking on the former does not launch the browser. Is there any way to ensure they are formatted, and work, as hyperlinks please? TIA V ...

Parse multiple text lines into 1 line in excel
help. I am an excel beginner and can't find out how to turn multipl lines of text into 1 row in excel. It's probably really easy but m manual is USELESS. Can anyone help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com debbie You're a little short on details. If nothing below fits the bill post back. "Multiple lines" is how many and is each line in a separate cell down one column? Do you want all lines to go into one cell? You can use this form...

calculation of cells
Periodically I open a work book and the calculation option has been changed to manual and I cannot figure out why. It seems that it would have to be done by a user and most of my spreadsheets are only used by me. Any ideas out there Mark, Calculation, auto or manual, is set by the first workbook that's opened. It is that way for any other workbooks opened in that instance of excel. Look for a workbook you might have opened first that's been set to Manual and saved that way. Go figure. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------...

returning vlookup values for blank cells
I have a spreadsheet that lists "soccer players" by name down the first colunm and "time in game" across the top and the position they play in array. I then use vlookup for another spreedsheet by "position" down the first column, time across the top and puts the players name into the positions. All this works fine. Since there are 5 more kids than positions, the orginal spreedsheet has blanks when the kids are out of the game. How do I use vlookup or other to extract the 5 sub'd out kids at the bottom of the 2nd spreadsheet? It only returns the nam...

Skip blank cells in diagrams
How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. You can include the function NA() in that field and the zero value for the data won't be displayed. "hlp" <hlp@discussions.microsoft.com> wrote in message news:4FF83D9F-F13E-4815-BDDE-26F44F2E6BE1@microsoft.com... > How do I exclude blank cells in diagrams. If I have an area of data and among > these data some is blank. How do I get excel to not display these data as '0...

Automating transfer of data in cells
I have a time management spreadsheet with data stored against work type and date. I need to transfer this data into a similar but more comprehensive spreadsheet and wonder whether it is possible to automate this task by using the work types and dates in a macro (I have almost 10 months of data to transfer), along the lines of check date, check worktype, where argument is true enter data from cell. I think I need to use visual basic, but I can't find out how in the help screens. Any advice is much appreciated. This is not difficult providing you keep your data in simple tables...